Negative numbers

[Guest]

Why does this formula produce a negative number when there are no negative numbers being used?
=SUM(B21:B28)-SUMIF(A21:A28,">=9",B21:B28)-SUMIF(A21:A28,"<=12",B21:B28)

 

[Peo Sjoblom]

Try this instead

=SUM(B21:B28)-(SUMIF(A21:A28,">=9",B21:B28)-SUMIF(A21:A28,"<=12",B21:B28))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Guest]

kennis,

The condition for column A has an overlap if any values are >=9 and <=12. So if you've got a large value in B with such an A value, you can be subtracting the same thing twice and wind up with a negative number.

A simple example:

A21:A28= 1,2,3,10,1,2,3,4

Almost anything can be in B. Here A=10 is the overlap. Everything else is below 12, so without the overlap you'd get zero. With the overlap you wind up with a negative of the value in B corresponding to A=10.

Art