Hey there stranger!

Sign up to get access.

3 Ways to Count in Google Sheets

About this Tutorial

The video explains three ways to count cells or information in Google Sheets: using the Explorer, using the COUNT function, and using the COUNTALL function. The Explorer is a tool located at the bottom right of the screen that allows you to quickly understand your data and find the count of items in a column. The COUNT function allows you to count the number of cells that contain numbers in a range of cells, while the COUNTALL function counts all the cells in a range, including those that contain formulas or are blank. The video also mentions other functions such as SUM and UNIQUE

Featured Formulas

Video Transcript

 Hello, welcome. In this video I'm gonna share with you three ways to count. This is Counting Cells or information. There is another formula called sum, if you wanna sum up things, and I won't go into that today, but I might mention it. The other functions I might mention the two function I'm gonna go over is count A or count all and count if but there's some other functions that might reference as unique.

And I think that's it. I might talk about some a little bit, but the first way to count things is this explorer, which is down here to the bottom right here. We can explore. So I've created a little situation here. I've already done some work up here. We have to say a bunch of people that we're reaching out to, maybe we're doing sales or something and we have our statuses in this column.

Maybe these are data validated or something. There's some dropdown menus who. Who knows, we, maybe we just need to know this information once a month. So we grab this information, copy paste in here and wanna parse through it or crunch through this data. And we wanna know how many people is there.

One thing is we can ho we can just go over the entire you can just highlight everything. Highlight these, and if we're our. Rows are not lined up for whatever reason. We can look over here and it says count 50 over here on the bottom right. So this explorer helps us really quickly understand our data.

If we don't know how many thing, how many things are in this column. That start, it says some here, but there's a down arrow here and there's actually, you can click here and find the count. There's some other things here. You can find the average, the MinMax count numbers, but for now we're just doing count.

What's interesting if you have not noticed yet, is these actually say something different. I'll show you why in a second when we get to that. Same with here, what we have some users, maybe each of these people. Our HR people and we're trying to sell them some service. Again, count is here, but some is here as well.

So we can, we have some interesting numbers. We have them average, we have a minimum maximum. We can quickly parse this data. If we just really want to know it, we don't need to show it. So that's one way to count. The other way is to use count all. So what I did here is to count all here across the top, but they're counting all from B3 and this when we're doing When we're doing count all, we have to be aware of literally what we're counting.

So if we go equals we go equals, let's actually delete that cause this is getting annoying. So we're gonna write count all here. So count all and we're going to do B, three to B, and we get 51. Why is that? Because we went from the header and it count. It doesn't know that it's. Header in, in Google Sheets, it thinks everything is data, right?

So we need to be aware of what are we counting. We actually need to go before, and we want to do that here. So now we have 50. Okay. And then if we copy and paste this across, we have 10, 50, 50. I like to do this sometimes put in the header counts. And there are some issues though with doing that. As you can see already, we use one row as data.

We might want to u show this as a dashboard somewhere. Maybe we want to copy and paste it and show it somewhere or visually represent it in some other way. For a variety of reasons. It doesn't work in the header, but sometimes it does. If we quickly wanna know how many things are in this column.

What I will point out though is that this column count of C, it doesn't count all does not count Blanks. But it also doesn't count formulas. If there's nothing in the formula, I've hidden here some, not everywhere, but somewhere formulas that say If this status B status is closed, then just give us whatever's in the D four column.

So if I change this to close now, suddenly that count changes cuz there's actually data here. It only works because this formula when. It's false. It literally does nothing. There's nothing here. If we had said, okay, give us a space now, suddenly we're, it's counting, but we don't see anything. So we're like where are those numbers?

We can't see anything. It's because there's an actual space here. And to Google Sheets, that's data. That is something. So we have to be vi vigilant about this. Whenever we see something like this, double check your numbers and double check that your formulas are actually doing nothing. We want them to do nothing.

Maybe we want to. Go across the entire thing and we now come back to 11 because these are all nothing. And we may be, oh wait, that was a mistake. Okay, that was supposed to be sent. We didn't get that number. Okay, great. We're at 10. Always double check your numbers that they apply to reality. Now the third thing, count F allows us.

To some different count different things. So say we wanted to count the number of we wanted to know what's our pipeline like, and we have all of these different. We have these statuses, right? Send closed in progress. One of the best ways to do this is on another sheet and just to quickly grab the the possibilities, I'm gonna go here and change this to b.

Four to B grab the unique so we know only sent close in progress right here, just as a have it. I'm gonna copy and paste values so that we don't change that just in case. And we're gonna use count if. Count if, what's the range? The range is the B column. We can and count if we can use the entire B column.

And what is the criterion is going to be where you can click on that c. Four there and it's 10 and 10 and 30. So we know this is correct or at least we assume this is correct cuz this adds up to 50. We can put a total here, put equals sum put here and we know. Okay, that adds up to 50 then that makes sense because there's 50 here and all of those combined would be 50.

We don't really have, we can also absolutely go in a little bit and count 'em and double check, but this is pretty spot on. We don't have to double check, but now we know out of this 50, there's 10 cent, 10 closed, 30 in progress. One just fun thing I like to do is I like to do a percentage right here next to things like this divided by some, or we can just do total and Yep, there's a problem.

And we get then change that to percentage or 20%. But when we copy and paste, there's a problem. We want to lock this one. So this is a little bonus there. We got 20%, 20%, 60 prints. So 60% of our leads are in progress. And that is explore count all and count if, and some problems you might run into with count All is let's go through 'em again.

So explore, we just select what we want to select. There and you know this count numbers is 10, count is 11, so it's, they actually knew this was a header cause it was text. If we just copy or highlight that we have count 10, we can do count all for the entire row and get that. Or if we mess up, if we include the header, it might be wrong.

We can also use count if on another page if you want, or even the same page if you really wanna put everything together and count if you have to have a criteria of what is the range and what are we searching for, what are we counting? Counting is gonna be very specific. Sent. Let me double check.

I think, let me see. Sent, does it? Yeah it will, it is it doesn't care about capitalization it, this is all the same. What might happen though, unique does care about it. Actually, this is gonna be a little interesting. So we go unique. And we use the same range. We actually do it on sheet one now, and when you, we do unique and there's two of the same status, but they're different.

Capitalizations, unique cha counts them as different. But if we do count, if sheet one, B2B and criterium is this notice that the count doesn't matter about capitalization. So our total is way off. Some, let's go to that, some here our total is 10 off. And so you can pretty much figure this out by look.

Absolutely there is sometimes when you have a unique different, unique and it's Dozens or thousands of uniques and you're doing account and like your counts off by a little bit, just double check this capitalization, if you're using unique, you have to be aware of that. And your account if doesn't care about capitalization, so it's gonna.

Add up these two things. Similarly, what I, oh, this is actually what I like to do to fix this is command C. I'm just pasting these values. I'll do this. I will go to data sort range by A to Z, and I'll notice these two are identical. You can absolutely find this out if all of these are supposed to be different counts or see it alphabetically that, oh, these are supposed to be the same thing, and so we just delete one of them.

From our count and we now have it fine. That's some pitfalls you might run into and some issues you might run into with explorer. Count all and count if count all will skip empty. Spaces, even if you have a formula that turns up empty. But make sure that formula turns up empty or it will be counted even if there's something like a space there and you just don't see it.

It'll still count it count if you have some issues, cuz you have to know that it doesn't care about capitalization, but something like unique does. Hopefully that was helpful to you. And now, are three ways to count anything in Google Sheets like.