Google Sheets – Count cells that are not blank

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

Longer formula
Longer formula

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.

 Live examples in Sheets

Go to this spreadsheet for examples of adding and subtracing days, months, or years that you can study and use anywhere you would like.

0 thoughts on “Google Sheets – Count cells that are not blank

Leave a Reply