Counting Blank, Non-Blank, and Non-Formula Cells Using Count Functions and Arrays

Counting blank and non-blank cells is fairly straightforward, but what about the cells that have formulas in them that equal no value. In some cases you might want a formula to be blank rather than equaling 0, especially if you have a lot of 0s, you want to focus your attention to the cells that have value, and that is easier to do when you have blank cells rather than 0s.

For example, see the table below:

In column A I have the numbers from 1 to 11 listed. In column B I have a formula to determine if the number in column A is even, and if it is, I will place the word EVEN as my result, otherwise it will be blank. The formula I used is the MOD function, which tells me how many remainders there are after dividing by a number. It has two arguments: the number I want to divide, and by what factor I want to do so. My formula in cell B2 looks as follows:

=IF(MOD(A2,2)=0,”EVEN”,””)

In the above example I am dividing cell A2 (1) by 2 and saying if it equals 0 (suggesting no remainder), then I want the result to return the word EVEN, otherwise I want the cell to be blank (“”). Since 1 divided by 2 does have a remainder, it is equal to “”. In the next row, number 2 does not have a remainder as it is an even number, so the result in column B is EVEN.
All the cells from B2:B12 have formulas, although some look empty.

Using COUNT, COUNTA, COUNTIF Functions

The conventional way to count non-empty cells is using the COUNTA function. A:
=COUNTA(B2:B12)
The above formula will return a result of 11, since all 11 cells in the range are not empty, which is correct. If I wanted to count how many cells had numbers, I would use the COUNT function. This won’t count text, however.
=COUNT(B2:B12)
The above formula yields a result of 0, since there are no numbers in that range, otherwise it would have worked fine. One workaround I could do is the COUNTIF function. I can count the number of blanks(“”) in the range:
=COUNTIF(B2:B12,””)
This returns a result of 6. I could combine the COUNTIF and COUNTA functions to arrive at my answer:
=COUNTA(B2:B12)-COUNTIF(B2:B12,””)
This will result in 11-6 = 5. This correctly tells me the amount of cells that contain values that aren’t formulas.
In Excel, there is usually not one way to solve a problem, so I’ll show you another way to accomplish this.

Using An Array Formula

The great thing about array formulas is they allow you to do multiple things in one formula that you couldn’t otherwise do with regular formulas. I am going to use the LEN function which tells me the length of a cell. If it is empty it would be equal to 0. If there is one letter, or one digit, LEN would equal 1. So I want to evaluate every cell’s length, and then tally all those who have a length of at least 1.
The LEN function would be as follows:
=LEN(B2)
This would result in a value of 0, since cell B2 has nothing in there. It is a simple function with only one argument as you can see. I will go a bit further and combine it with an IF function to return a value of 1 if there is something in the cell, and a value of 0 if there is not.
=IF(LEN(B2)>0,1,0)
The last step is to use the SUM function to now total all these values. If the non-empty cells return values of 1, then I just need to sum them all to get my count. The formula (before turning into an array) looks like this:
=SUM(IF(LEN(B2:B11)>0,1,0))
All I have added is the SUM function before my IF function as well as an additional closing parentheses. Now this formula won’t work as is since it is not yet an array. To turn it into an array formula, when editing the cell I have to click CTRL+SHIFT+ENTER and my formula will now look as follows:
{=SUM(IF(LEN(B2:B12)>0,1,0))}
This will return a result of 5 which correctly tells me the same result as where I combined the COUNTA and COUNTIF functions.
Below is a summary of the results