2
votes

I have a range of cells that all contain if statements. These if statements are basically: if a reference cell is empty, leave the cell containing the reference formula blank, if reference cell is populated, populate the reference formula cell. An example is below that would be located in B1 as an example.

IF(A1="", "", A1) 

I want a way to count the number of blank cells. Ordinarily I would use COUNTA, but that counts the number of empty cells, not the number of blank cells, and since all of the cells are populated with IF statements, it sees them as not empty, even though they are blank. Is there a function that counts the number of blank cells rather than COUNTA which counts the number of empty cells? I tried using COUNTIF, like I have below, but that looks for a condition that is true and in order to specify not true, it needs to be surrounded by quotes which messes up the concept of using quotes to represent a blank cell.

=COUNTIF(range, "<>""")
1
=COUNTIF(range, "<>")Scott Craner
That still counts the number of cells that contain formulas.User247365

1 Answers

3
votes

We can distinguish formulaic nulls from empties as follows:

Say we fill B1 through B8 like:

enter image description here

leaving both B9 and B10 deliberately empty.

Now both:

=COUNTBLANK(B1:B10)
=SUMPRODUCT(--(LEN(B1:B10)=0))

will report 5 (three formulaic nulls and two genuine empties), but:

=SUMPRODUCT(--(ISBLANK(B1:B10)))

will report 2 for the two genuine empties.

Note that we can subtract the third formula from either of the first two to get the number of formulaic nulls.