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)
Microsoft Excel
Last Comment
zorvek (Kevin Jones)
ASKER
Thanks Kevin, why is there multiplication going on there?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
This will also work:
=SUMPRODUCT((F3:F100<=50),
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),
=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,"<=
=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,"<
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
=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 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,"<
the second element is the result of.....
=SUMIFS(F3:F100,G3:G100,"<
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
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,"<
the second element is the result of.....
=SUMIFS(F3:F100,G3:G100,"<
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 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
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)*(
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