Hey there stranger!

Sign up to get access.

How to Count Numbers in a Row

About this Tutorial

Got this question in the Facebook Group: How to count the number of numbers in a row. I saw two possible solutions mentioned in the comments and wanted to go through why those are okay but there is a much more flexible and better solution.

Video Transcript

0:00 Hey, so here's an interesting question actually this question comes from Benjamin over at I love Google Sheets. It's a free Facebook group called I love Google Sheets and Benjamin's asking this question Basically, they have a set of series of numbers, and they want to know how many of these numbers are
0:16 between 1 and 16 and so there's some interesting wrong answers here and I want to show you how and why they're wrong and what might be the correct answer, might be because this question comes sort of with some very variables that are missing, but uhm, here's what they want, I want to count the number
0:38 of values entered into these columns with each row in the B column, uhm, that's it, that's the question, but I think they meant in the row, uhm, I've created an example of a desired function, the outcome would like to, would be automated, so they have eight and fourteen, so see up here, I recreated this
1:01 and they have this one one three three four, uhm, seems like some kind of score for a round perhaps, uhm, and they're sort of just counting here, how many there are, there's no out of bounds, so, uhm, look, you can do count.
1:20 And you just sort of select a count of, let's see, let's have this, and that's gonna get you eight and fourteen, uhm, but there's another, someone else said, so I think count is the correct answer here, two You some extent, but there are some variables that I think you could enter that might be more 
1:41 interesting to make this function more flexible. Leo mentioned count if, so let's look at count if and see if that's a possibility.
1:48 Count if, and what we're gonna do is we have a range, so we're just gonna select this b through q count if.
1:56 And the criterion needs to be something, so we can set this criterion let's say greater than zero, and, and we're gonna get the same answer.
2:04 Great! But what if there is like, you wanna count less everything less than ten, or ten or less, or sixteen or less, and there's some other numbers here.
2:16 So like here's twelve, and if we wanted to say ten or less, or between one and ten this count would be seven, so how do we get that?
2:23 Count if. If is only one criterion, you can't add another criteria here, so we can not say less less than ten.
2:33 You're gonna get an error here, there's a problem, it's a formula parse error, and what you might think if you know about out.
2:40 Formula combinations, you might think oh let's put an and there, and that doesn't work either. We're just gonna get zero.
2:48 What I like to do is use filter in this case, because you can have multiple conditions. And so we're gonna take this range filter off.
2:56 All of this, uhh the condition is that this, anything here is less than 10, everything b2q2 is greater than zero.
3:10 And what we're gonna get, we're actually gonna get the uhh a list of answers. Umm so what we wanna do is umm what is it, uhh count all.
3:24 And there we go, so we wrap filter with count all and now we have 7. Thanks watching. Bye. And that is the answer, right?
3:29 That is the answer between 10, one in 10 or zero in 10, right? And we can do less than and equal to 10.
3:37 We can even include the top number here like 12 and we'll get the right answer. And again we get the right answer here.
3:44 Your way. With count all wrapped around, filter, and have multiple conditions. And I think this is a much more flexible and advanced use of formulas here to get the same answer, right?
3:56 We got the answer with count, but it's only gonna just count the number of variables. We'll see we may, in some future use of this formula, want to see some bounds here, right?
4:09 Maybe there's like numbers that are highest and lowest, and we wanna take those out, we just wanna find this sort of range of variables.
4:16 So I thought that would be interesting to show you this count or filter combination as an answer that is, again, in this particular case is equal to count.
4:27 And the count function is quite simple it's just counting, we can Thank you. And look at the help here. It's quite simple.
4:36 It just returns a number of numeric values in a data set. So if it's a number, it'll return it. And it returns the number of them, not the sum of them.
4:45 So you might be looking at this and be like, oh, some Thanks watching! b3 to bq and that's 78. That's definitely the wrong answer here, right?
4:53 Sum. And count if has its issues. But also I think this count all and our count a and wrapped around filters is like the perfect solution to this because it's just got so many possibilities from this action.
5:08 Thank you Benjamin for the question and I love Google Sheets and if you are not a member of I Love Google Sheets yet go check it out facebook.com slash groups slash I Love Google Sheets.