Countif Not Blank does not consider cell as blank if there is a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3285032%22%20slang%3D%22en-US%22%3ECountif%20Not%20Blank%20does%20not%20consider%20cell%20as%20blank%20if%20there%20is%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285032%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EIssue%3A%20COUNTIFS%20formula%20won't%20count%20non%20blank%20cells%20if%20the%20non-blank%20cells%20are%20a%20result%20of%20a%20formula.%20%3CSPAN%3EInstead%2C%20COUNTIFS(range%2C%22%26lt%3B%26gt%3B%22%2C%20etc.)%20counts%20all%20the%20cells%20with%20a%20formula%20in%20them%2C%20which%20means%2C%20all%20of%20them.%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3EI%20use%20the%20formula%3A%26nbsp%3B%26nbsp%3B%3DIF(ISNUMBER(SEARCH(%22DM%20-%20%22%2CC1))%2CC1%2C%22%22)%20in%20the%20column%20where%20the%20non-blank%20cells%20need%20to%20be%20counted.%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BThe%20result%20can%20be%20either%20a%20text%20string%20or%20nothing%20(%22%22)%20How%20to%20I%20count%20the%20number%20of%20cells%20with%20a%20textstring%20(and%20other%20criteria%20hence%20the%20countIFS)%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3EThanks.%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3285032%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285089%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20Not%20Blank%20does%20not%20consider%20cell%20as%20blank%20if%20there%20is%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F996310%22%20target%3D%22_blank%22%3E%40Elva_Tanguerre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20more%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20ROWS(range)*COLUMNS(range)%20-%20%20COUNTBLANK(range)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Etaking%20into%20account%20that%20COUNTBLANK%20counts%20both%20blank%20cells%20and%20cells%20with%20empty%20strings.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3285061%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20Not%20Blank%20does%20not%20consider%20cell%20as%20blank%20if%20there%20is%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3285061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F996310%22%20target%3D%22_blank%22%3E%40Elva_Tanguerre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20390px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F363962iD6209F4644BB697C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%26nbsp%3B%3CSTRONG%3EB1%3C%2FSTRONG%3E%20(indicator%20only)%20and%20down%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DISBLANK(A1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%26nbsp%3B%3CSTRONG%3EE1%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUMPRODUCT(--(A1%3AA9%26lt%3B%26gt%3B%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%26nbsp%3B%3CSTRONG%3EE2%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUMPRODUCT(--(A1%3AA9%3D%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Issue: COUNTIFS formula won't count non blank cells if the non-blank cells are a result of a formula. Instead, COUNTIFS(range,"<>", etc.) counts all the cells with a formula in them, which means, all of them. 

 

I use the formula:  =IF(ISNUMBER(SEARCH("DM - ",C1)),C1,"") in the column where the non-blank cells need to be counted. The result can be either a text string or nothing ("") How to I count the number of cells with a textstring (and other criteria hence the countIFS)?

 

Thanks. 

 

 

2 Replies

@Elva_Tanguerre 

 

_Screenshot.png

In B1 (indicator only) and down:

=ISBLANK(A1)

In E1:

=SUMPRODUCT(--(A1:A9<>""))

In E2:

=SUMPRODUCT(--(A1:A9=""))

 

@Elva_Tanguerre 

One more variant

= ROWS(range)*COLUMNS(range) -  COUNTBLANK(range)

taking into account that COUNTBLANK counts both blank cells and cells with empty strings.