• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

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)

Open in new window

0
shampouya
Asked:
shampouya
  • 7
  • 4
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
=SUMPRODUCT((F3:F100<=50)*(G3:G100=G2:G99)*(G2:G99=G1:G98)*F3:F100)

Kevin
0
 
shampouyaAuthor Commented:
Thanks Kevin, why is there multiplication going on there?
0
 
zorvek (Kevin Jones)ConsultantCommented:
I'm using SUMPRODUCT versus SUMIF. SUMPRODUCT operates in an array mode and I prefer to build a single parameter versus multiple parameters. The way it works is each part produces a Boolean which, when multiplied by another Boolean, produces a 1 or 0:

FALSE * FALSE = 0
TRUE * FALSE = 0
FALSE * TRUE = 0
TRUE * TRUE = 1

The result is finally multiplied by the value in column F to get the answer.

Kevin
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
zorvek (Kevin Jones)ConsultantCommented:
This will also work:

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

Kevin
0
 
shampouyaAuthor Commented:
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)
0
 
shampouyaAuthor Commented:
And when you write G3:G100=G2:G99, is that comparing the contents of the two arrays in their entirety? Or cell by cell?
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
shampouyaAuthor Commented:
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)
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
barry houdiniCommented:
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,"<=50",G3:G100,G2,G2:G99,G1)

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

=SUMIFS(F3:F100,G3:G100,"<=50",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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
barry houdiniCommented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now