Countif function

New Contributor

Why does the countif function see a formula in a cell as data, and include it in the count?

7 Replies

@BobinEssex 

COUNTIF looks at the value of cells, not at their formulas.

It definately sees a formula in a cell as 'data'. I have an example that proves it

@BobinEssex 

Sorry, what exactly do you mean by that? Could you provide an example?

I could send you the spreadsheet example I have

@BobinEssex 

You can upload the workbook to OneDrive, google Drive, Dropbox or similar, get a link to share it and post that link in a reply.

If you'd rather not do that, you can email the workbook to hans dot vogelaar at gmail dot com

Hans, sent you an email

@BobinEssex 

I see what you mean now.

You have turned off the setting "Show a zero in cells that have zero value" in File > Options > Advanced > Display options for this worksheet.

If you turn it on, you'll see that the formulas in I4:J5 return 0. That's why they are included in the count.

The reason for this is that Excel sees an empty cell as 0 in formulas if you don't explicitly treat it as text.