Strange Count Issue

[criticalchain96]

Hello, I am trying to count only values that ="1" across 3 different business divisions (A, B, and C); however, for some reason it seems to be including negative values.

=COUNTIF(K5:K8382,"=1")

I'm not sure why this is the case since it is specified to only count those that equal 1.

How do you eliminate the negative values from the count?

Thanks!

 

[VoG]

Try

=COUNTIF(K5:K8382,1)

 

[barry houdini]

Why do you think it's counting negative values? Do you have other values in the range that aren't numbers?

 

[RoryA]

Are you sure the values are really negative and not just formatted to display that way?

 

[criticalchain96]

Hmm okay I think I see what is wrong - in some cases it is dividing a divisions sales (sold at a loss) by the total sales for that customer (also at a loss) and coming up as a positive amount. This is fine if it is consistent.

It seems like I'll need to do something like =COUNTIF(B8362:D8362, abs >0)

However, the syntax for the abs piece is off. Can you help with this?

 

[RoryA]

You'd need SUMPRODUCT:

=SUMPRODUCT(--(ABS(B8362:D8362)>0))

 

[criticalchain96]

You'd need SUMPRODUCT:

=SUMPRODUCT(--(ABS(B8362:D8362)>0))

Thanks! This fixed it! What is the logic behind using sum product for this?

 

[shg]

All of the COUNTIF and SUMIF functions require a range, not an array, as the argument, and "abs>0" is not a valid expression.