Countif function

%3CLINGO-SUB%20id%3D%22lingo-sub-2821094%22%20slang%3D%22en-US%22%3ECountif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2821094%22%20slang%3D%22en-US%22%3E%3CP%3EWhy%20does%20the%20countif%20function%20see%20a%20formula%20in%20a%20cell%20as%20data%2C%20and%20include%20it%20in%20the%20count%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2821094%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2821747%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2821747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1178070%22%20target%3D%22_blank%22%3E%40BobinEssex%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECOUNTIF%20looks%20at%20the%20value%20of%20cells%2C%20not%20at%20their%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822653%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822653%22%20slang%3D%22en-US%22%3EIt%20definately%20sees%20a%20formula%20in%20a%20cell%20as%20'data'.%20I%20have%20an%20example%20that%20proves%20it%3C%2FLINGO-BODY%3E
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.