Bjarne Asks: Can I show the total number of rows minus the header row?

Beginner

About this Tutorial

find out how to show the total number of data rows available in a sheet.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

COUNTA() COUNTBLANK() SUM()

Video Transcript

<div> Hello. PR asks show the total number of rows in the sheet minus the header , or how do I show the total number of rows in the sheet minus the header row ? So if we have a header row here of name email, something like this we are gonna have. A number of rows down here, but what's gonna be interesting is if we use equals count all. So let's, let's get the row count, let's put that here. And then we do equals count all. And if we do a to a this is definitely the wrong number .

It's one which is only the header row .  Or what we have to do really is use a couple of different count methods, so count blank and we do a two today. , and that gives us 999. But if we have you know, names here, suddenly our, our our our count is going down, right? This is not the number of rows anymore. It's the number of blanks. So what we can do is we can and let, let's look at this. If we now have count all a two to a. . That's three. Well, those numbers combined, right? Equals count blank. A two to a, these numbers combined gives us the actual row count.

So we can actually combine these here. Let's do that right now. So we're gonna do some, and we're gonna have a common between each one. And we're gonna do count blank, a two to a. Count all, count A, A two di a. And now no matter what we have here, Carlos, you know we, we have some names here. This adding information here doesn't change this row count, but going down, let's go to the bottom. Let's add a thousand rows and let's go back up to the top. And now our count is correct, 1090. Right, so we don't have to go down here to see how many rows we have. Let's see.

Can we add another, maybe add 10,000. And there we go. We have 12,099. So this is all of the rows minus the header , right? Because we did a two, we got out of the header and and it gives us everything. Count, blank count, all combined. Give us, give us the row count. You can also format this number here with this one. . There you go. And you have 12,999 rows in this sheet bike.</div>