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
[TABLE="class: grid, width: 74"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Data[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Calculation example in c1

[TABLE="class: grid, width: 499"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Countifs Criteria[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD]=COUNTIFS(Sheet1!$A$4:$A$13,Sheet1!$C4)[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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....!