Excel Countifs function showing wrong values on coulumns with different datatype

What are you counting? Can you upload a sample workbook and explain how the countif is not working as desired?

Hi Subodh,

I have attached an excel file, I have colored a portion on its first sheet. In that colored portion, first cell gives the count of non blank cells, when I filter out blank values from column A. 2nd value on the colored portion gives the number of values in column B when I filter out  blank values from column A and then Column B. Similarly for 3rd and 4th cell on colored values. But when I count the values on the 4th column by filtering out blank values in all other previous 3 columns, the count is wrong.

Please have a look.

Thank you

Yes, you are right. The CountIfs behavior is strange for some reason, maybe not handling more than 3 criteria or something like that.
But you can use the Sumproduct function instead...

=SUMPRODUCT((A$2:A$1111<>"")*(B$2:B$1111<>"")*(C$2:C$1111<>"")*(D$2:D$1111<>""))

Select allOpen in new window

kk.xlsx

The criteria in the COUNTIFS are treated as AND criteria.

So the count in cell H15 is counting where column A is not blank AND column B is not blank. 6 is the correct result, rows 2 - 6 and row 9.

Count in H16 is looking at A<>"" AND B<>"" AND C<>"". Correct result of 2, rows 4 and 6.

Count in H17  is wrong because D4 is not blank, there is an apostrophe in the cell. Select D4 and press Delete key. Count goes to 1.

Autofilter recognises the apostrophe as blank even though it isn't really.

Good catch Rob!