EXCEL -- formula for SUMIF

  Dick9 08:11 23 Feb 05
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 05

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 05

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 05

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 05

Use SUMPRODUCT

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

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…