shampouya

asked on

# How do I add these three criteria to my SUMIFS formula in Excel 2010?

I'm trying to write a formula in column G that basically says "Sum everything in column F where the value is less than or equal to 50, and where the value in column G equals the value of the cell that is 1 cell above the current cell, and where the value of the cell 1 above the current cell equals the value of the cell 2 cells above the current cell". When I drag my formula down from G3, it does not seem to be working as I expected. Every cell is showing up as 0. How should I fix my formula below?

`=SUMIFS(F:F,G:G,G2,F:F,"<=50",G:G,G2=G1)`

ASKER

Thanks Kevin, why is there multiplication going on there?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

This will also work:

=SUMPRODUCT((F3:F100<=50),(G3:G100=G2:G99),(G2:G99=G1:G98),F3:F100)

Kevin

=SUMPRODUCT((F3:F100<=50),

Kevin

ASKER

Do I need any dollar signs for column F when I drag the formula down? Something like this, since I want to total everything in column F:

=SUMPRODUCT((F3:F100<=50),(G3:G100=G2:G99),(G2:G99=G1:G98),F$3:F$100)

=SUMPRODUCT((F3:F100<=50),

ASKER

And when you write G3:G100=G2:G99, is that comparing the contents of the two arrays in their entirety? Or cell by cell?

Cell by cell. The focus array is G3:G100. The array being compared is G2:G99. SUMPRODUCT takes each row of each array and does the calculation, then sums the results.

Kevin

Kevin

ASKER

I like the boolean multiplication concept, but would it be possible to make it work with a formula like the one below (starting in row 3)? I ask because my coworkers are used to sumifs and they would freak out if they saw sumproduct.

=SUMIFS(F:F,G:G,G2,F:F,"<=50",G3:G100,G2:G99)

=SUMIFS(F:F,G:G,G2,F:F,"<=

This will work but, because the third, fifth, etc. parameters have to be single cell references or constants, you have to enter the formula as an array formula by pressing CTRL+SHIFT+ENTER:

=SUMIFS(F3:F100,G3:G100,"<=50",G3:G100,G2:G99,G2:G99,G1:G98)

Array formulas are strange too. Choose your poison and move on to the next project ;-)

Also, the formula I posted above won't work:

=SUMPRODUCT((F3:F100<=50),(G3:G100=G2:G99),(G2:G99=G1:G98),F$3:F$100)

It should be:

=SUMPRODUCT((F3:F100<=50)*(G3:G100=G2:G99)*(G2:G99=G1:G98),F$3:F$100)

Kevin

=SUMIFS(F3:F100,G3:G100,"<

Array formulas are strange too. Choose your poison and move on to the next project ;-)

Also, the formula I posted above won't work:

=SUMPRODUCT((F3:F100<=50),

It should be:

=SUMPRODUCT((F3:F100<=50)*

Kevin

Hello Kevin/shampouya

I don't believe the SUMIFS version will work

In

=SUMIFS(F3:F100,G3:G100,"<=50",G3:G100,

the second element is the result of.....

=SUMIFS(F3:F100,G3:G100,"<=50",G3:G100,

etc.

Exactly which value you see in the cell can depend on

regards, barry

I don't believe the SUMIFS version will work

In

**SUMPRODUCT**when you use G3:G100=G2:G99, it's doing a "one-to-one" comparison as you said, comparing G3 to G2, G4 to G3, G5 to G4 etc. but in**SUMIFS**when you have G3:G100 as the range and G2:G99 as the criteria then each value in G2:G99 is being compared against each value in G3:G100, so the above SUMIFS formula actually returns an__array of 98 values__, the first number in the array represents the result of this formula:=SUMIFS(F3:F100,G3:G100,"<

**G2**,G2:G99,

**G1**)

the second element is the result of.....

=SUMIFS(F3:F100,G3:G100,"<

**G3**,G2:G99,

**G2**)

etc.

Exactly which value you see in the cell can depend on

**where**you place the formula......but except by co-incidence I don't think you'll get the required results

regards, barry

Which is why you have to enter it as an array formula so the third, fifth, etc. parameters are treated as arrays and passed to SUMIFS as multiple individual values.

Kevin

Kevin

Hello Kevin,

Are you suggesting array entering in a

Either way it doesn't change my main point - I don't think you can use SUMIFS for this type of problem - you'll get different results using SUMIFS, and not the same results as the SUMPRODUCT version that works.

regards, barry

Are you suggesting array entering in a

**range**or a single cell? - the former is the only way you can see all the 98 values generated.Either way it doesn't change my main point - I don't think you can use SUMIFS for this type of problem - you'll get different results using SUMIFS, and not the same results as the SUMPRODUCT version that works.

regards, barry

I tried to reproduce the results I got yesterday with the SUMIFS and could not - not sure what I did but Barry is correct, the SUMIFS function does not play in an array formula world. I also re-read the question and realized I was approaching the problem incorrectly. I had assumed you wanted a single sum of the entire column F given the condition.

My current interpretation of the question is that you want to, on each row and in column G, sum all of the values in column F where the value in F is less than or equal to 50 and the value in column G on that same row is equal to the value in column G in the above two rows.

Your original formula references all of column F and column G. That results in a circular reference (you are evaluating over the same cell in column G that you are trying to calculate.) The only way this makes any sense is if we limit the bottom of the evaluation area to the row above the row being calculated.

This formula, placed in cell G4 and then copied down will do that:

=SUMPRODUCT((F$3:F3<=50)*(G$3:G3=G$2:G2)*(G$2:G2=G$1:G1)*F$3:F3)

Further consideration of the question leads me to this question back to you: what the heck are you trying to do? The results of this formula don't seem to provide any real value as you are summing based on the sums in other rows.

Are you sure you posed the question correctly?

And, just to be clear, Barry is correct, SUMIFS cannot do what you want.

Kevin

My current interpretation of the question is that you want to, on each row and in column G, sum all of the values in column F where the value in F is less than or equal to 50 and the value in column G on that same row is equal to the value in column G in the above two rows.

Your original formula references all of column F and column G. That results in a circular reference (you are evaluating over the same cell in column G that you are trying to calculate.) The only way this makes any sense is if we limit the bottom of the evaluation area to the row above the row being calculated.

This formula, placed in cell G4 and then copied down will do that:

=SUMPRODUCT((F$3:F3<=50)*(

Further consideration of the question leads me to this question back to you: what the heck are you trying to do? The results of this formula don't seem to provide any real value as you are summing based on the sums in other rows.

Are you sure you posed the question correctly?

And, just to be clear, Barry is correct, SUMIFS cannot do what you want.

Kevin

Kevin