If you’re using Google sheets and you want to count the number of cells if they’re not blank, there are a couple different ways to do it and some complications that you might run into.
COUNTA usually works
I’m going to show you the easy way and a little bit harder way if you run into trouble. Let’s use COUNTA with the range from B3 to B9. The syntax, as also shown in the image above and the linked Google Sheet, is =COUNTA(B3:B9). This is the easy, straightforward way that’ll probably work most of the time. COUNTA is a built-in function. It will count any cell with a value and within the specified range. If that works for you, then you’re done. Don’t worry about it.
Video explanation
But, if we go to column C, things can get a little bit more complicated. This column looks like it has the same number of values, right? However, the COUNTA has the same range but is picking up seven instead of six. That’s happening because I snuck a blank space in C5. You may want to count this blank space since it is technically a value even though you cannot see it. If that’s the case, then then you’re done. COUNTA will work and the result is seven.
Longer function to not count white space
But, you may not want to count this blank cell. Accordingly, you may want to count what appears to have data in it, and you don’t really consider an empty space to be data. If this is the case, you have to use a more complicated formula. The function in cell C12, =SUMPRODUCT(--(LEN(C3:C9)>0)) is adding everything when the LEN function returns something greater than zero. LEN is counting the number of characters. The LEN function will not count the spaces that don’t really have any characters and, if that’s what you want, then that’s the way to get it done.
Now you have two different choices to count the number of cells that are not blank. A simple COUNTA for cells with no values at all, and the longer alternative, LEN, to exclude the empty spaces from the count.
Tav-El says:
Thanks! This was really helpful.
Prolific Oaktree says:
You’re welcome. I’m glad you liked it!
Steve Kelem says:
A couple of typos: ussualy and excude
Prolific Oaktree says:
I fixed them now. Thanks for pointing them out.
Elisha says:
whats the formula if I need to count 3 specific words within 1 column. For instance I need 1 total for how many times educator, principal and superintendant is entered in column D.
Appreciate any help thank you.