countifs blank zeros.

[cud22]

Have spent the last two hours losing the plot trying to find why a countifs is returning unexpected result, then discovered that countifs treats blanks and zeros the same...

One of the columns i am using in the countifs has both blanks and zeros that i need to differentiate. How would you do this?

Unfortunately I do not have control over the data, and simply amending the data to fix wont work as the data will be constantly reloaded....

Example column in a1

Data
0
1
2
3
0
1
2
3

<colgroup><col></colgroup><tbody>
</tbody>

Calculation example in c1

Countifs Criteria	Result
	2	=COUNTIFS(Sheet1!$A$4:$A$13,Sheet1!$C4)
0	2
1	2
2	2
3	2
99	0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Changing a zero to 99 in the data table reduces the result of the countifs formula for both blank and zero, how can i avoid this?

PS, this is just an example table, in the original spreadsheet the countifs is looking at 4 fields, one of which contains blanks, zero's and positive integers; all others contain text and blanks.

 

[Special-K99]

With "Data" in A1 and "Countifs criteria" in C1

Use this formula instead

=SUMPRODUCT((A$2:A$11=C2)*(LEN(A$2:A$11)=LEN(C2)))

 

[cud22]

Thanks,that works as expected.

Was going round in circles finding the error in my formula / data before realising there was no error and the countifs formula was working as designed (but not as expected by me....!