EXCEL -- formula for SUMIF

  Dick9 08:11 23 Feb 2005

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

This formula works ; to wit,

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 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


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



  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"



  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


  VoG II 15:55 18 Mar 2005


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 ?