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’s a couple different ways to do this and some complications that you might run into.

COUNTA ussualy 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 sytax, 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 an 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 of seven.

Longer function to not count white space

But, if you don’t want to count this blank cell, if you just want to count what appears to have data in it, and you don’t really consider an empty space to be data, then you have to use a little bit more complicated formula. If you look at the function that’s in cell C12, =SUMPRODUCT(--(LEN(C3:C9)>0)) it is saying add up everything when this function – LEN returns something greater than zero. This function is counting the number of characters. The function will pull out 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 to not count cells with empty spaces

Follow image below for the live Google Sheet with this data

docs share icon