Counting cells with formula results but not formulas

[teachman]

Hello,

This is driving me buggy. Let's say cells B1 through B10 have the formula: =IF(A1>1,"Y",""). Of course the formula would change from cell to cell A2, A3, ect.

Now, let's say that cell A5 = 5. This means that cell B5 = Y. So, if I counted the cells B1 thru B10 (a total of 10 records) that are blank, using COUNTBLANK I would expect the formula: =COUNTBLANK(B1:B10) would yield 9. However, in reality, Excel tells me the answer is 0 (zero). I assume that Excel is counting the fact that there is a formula in a cell meaning the cell is not BLANK. I'd agree it wasn't EMPTY, but that the formula in the cell evaluates to null which would be BLANK.

How can I get the answer I'm looking for, which is 9? Now, in reality, the formula in B1 through B10 is not as simple as in the example and doesn't just return a "Y" or " ", so I can't use COUNTIF.

There are a lot of instances where I would like to have Excel ignore the fact that there is a formula in the cell and have it believe if the formula returns a null, i.e. "", then for my purposes, the cell is blank. Not empty, because there is a formula, but blank.

Thanks,

George Teachman

 

[bbott]

Maybe:

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

or

=COUNTIF(B1:B10,"")

 

[teachman]

bbott,

Both of those do the job. As a one step above novice in Excel, I understand the second suggestion and can't even begin to understand your first suggestion. Looks like I have something new to learn. Thanks!

 

[bbott]

You're welcome.