# Counting cells with formula results but not formulas

#### teachman

##### Active Member
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Maybe:

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

or

=COUNTIF(B1:B10,"")

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!

You're welcome.

Replies
2
Views
459
Replies
1
Views
251
Replies
0
Views
387
Replies
3
Views
397
Replies
8
Views
226

1,196,510
Messages
6,015,620
Members
441,909
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back