SUMIF Conditions

H

hamblin.adam

=SUMIF($F$6:$F$2000,$O8,$G$6:$G$2000)*AND($M$6:$M$2000<=60)

this function ignores the AND statement.....Please Help

Thanks,
Adam
 
J

JE McGimpsey

Not sure what you mean by "ignores"...

The AND() statement will return TRUE if ALL values in M6:M2000 are <=60,
FALSE otherwise. The multiplication operator (*) will cause that value
to be coerced to 1/0 respectively, so if all values are <=60, the value
of the SUMIF() will be multiplied by 1. Af all the values are NOT <=60,
then the result of the SUMIF() will be multiplied by 0.

IF, OTOH, you're trying to sum the values in G if the values in F = O8
and the values in M are <=60, try

=SUMPRODUCT(--($F$6:$F$2000=$O8),--($M$6:$M$2000<=60),$G$6:$G$2000)
 
P

Peo Sjoblom

It's because there are no AND statements in SUMIF or COUNTIF

=SUMPRODUCT(--(M6:M2000<=60),--(F6:F2000=O8),G6:G2000)

should work
 
Top