# EXCEL -- formula for SUMIF

Dick9 08:11 23 Feb 2005
Locked

I want to sum up the numbers in column J conditionally if BOTH of 2 separate conditions are met.

This formula works ; to wit,
SUMIF(b2:b99,>1,j2:j99)

But how should one put in "and" so that it also includes f2:f99, >3 , j2:j99

The sumnation would then be of fewer cells than either condition on its own; and the count would also be fewer.

Dick9 I use windows 98.

Simsy 09:06 23 Feb 2005

you can have 2 conditions in a "sumif"

What you can do however, is have an additional colum, that has the conditions in, giving a true/false answer, and have a sumif reffering to this range...

eg, in your example (I'll use row K as the extra row);

have in K2 the formula;

=AND(B2>1,J2>3)

and copy this down to K99.

For each row the at both conditions are met the value in K will be "TRUE"

So now you can make your sumif formula

=sumif(K2:K99,TRUE,B2:B99)

I hope this helps. My apologies if there is a simpler way to do it!

Regards,

Simsy

Simsy 09:08 23 Feb 2005

this line

For each row the at both conditions are met the value in K will be "TRUE"

should read

For each row where both conditions are met, the value in K will be "TRUE"

Regards,

Simsy

howryou 14:45 18 Mar 2005

Let me clarify what I think that you are wanting to do first

If(the sum of range B2 to B99 > 1) and (the sum of the range F2 to F99 > 3) then display (the sum of the range J2 to J99)

The formula for this would be

=IF(SUM(B2:B99)>2,IF(SUM(F2:F990)>3,SUM(J2:J99)),FALSE)

VoG II 15:55 18 Mar 2005

Use SUMPRODUCT

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iPhone X review

Political cartoons in 2017: Chris Riddell, Rebecca Hendin and Dave Brown on what it’s like to…

The best iPhone for 2017

Tennis : comment regarder la finale de la Coupe Davis 2017 ?