SUMIFS with the Second Critera being a Range of Values?

Hi, Curious how/if I can do this:

SUMIFS(AircraftData!$H$2:$H$301,AircraftData!$E$2:$E$301,$D3,AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B)

I realize I could spell out every possible value (about 35 unique values) that could appear in ConstrainedReqs$B:$B, but I was wondering if there were an easier way to do it without having to hard-code all the possible values that could appear in column B?
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
To answer your question you can't do this by SUMIFS first because SUMIFS work on functionality of And..and what i understand you want to apply or here where in your data if the values equal any of those 35 unique values you want to sum them...

So now if that's what you are trying to do then you can do by SUMIF like i explained above..You need to use a different formula for the same...or apply multiple times sumif rather then 1...

Second you need to type all those 35 values in the formula which you want to compare or i can write a UDF for you to do this..Can you post your sample workbook as it will be easy to work on the same..

Saurabh...
-PolakAuthor Commented:
Yes, I think we understand eachother,  the problem is the sum range has ALL of those 35 unique values. However, Column B on the ConstrainedReqs worksheet may only contain 5 or 6 or 10 at any given time. And Column B changes constantly based selections made by the user.

I'd like to stay away from a UDF/Macros for now (I think).... Also, it is difficult for me to post the real workbook because i have to do a lot of data-sanitizing..... If I'm understanding you correctly you're solution would be to do SUMIF + SUMIF + SUMIF and hard-code the 35 values?
Saurabh Singh TeotiaCommented:
Yes that's one way or alternatively you can use sum product which will be...


=Sumproduct((Your Criteria1)*((Your Unique Criteria1)+(Your Unique Criteria2))*(Sumrange))

The + in the sumproduct will work as an or and you can apply this in one formula only and it will do SUMIFS as you can add as many criteria in the same...

Saurabh...
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

-PolakAuthor Commented:
Humm I've attached a dummy workbook for your assistance.

A long time ago I did something similar with an Array and the countifs function. It looked like this:

SUM(COUNTIFS(P:P,"A",O:O,{"B","C","D","E"}))

Can you SUM(SUMIFS the same way?

If not just provide your solution and I'll roll with it...
SUMIFSformulaEESAfe---Copy.xlsx
Saurabh Singh TeotiaCommented:
Here a copy for you reference..Just did the same with 2 formulas..both answer are same...

Saurabh...
SUMIFSformulaEESAfe---Copy.xlsx
Saurabh Singh TeotiaCommented:
Did with one more formula which i think you gonna use and love the most as this is exactly what you were looking for...

Saurabh...
SUMIFSformulaEESAfe---Copy.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
Hey Saurabh!

I was just going to post an ISNUMBER/MATCH version - I think that's the way to go here - it can cope with duplicates without double counting (unlike SUM(SUMIFS) and you can have blanks in the B1:B13 range.

....and it's more efficient than your other SUMPRODUCT option

regards, barry
Saurabh Singh TeotiaCommented:
Hi Barry,

Thats what i did the last formula which i posted was isnumber/match only..

=SUMPRODUCT((AircraftData!$E$2:$E$21=AnalysisEngine!D3)*(ISNUMBER(MATCH(AircraftData!$D$2:$D$21,ConstrainedReqs!$B$2:$B$13,0))*(AircraftData!$H$2:$H$21)))

I was trying this but was messing up in the match part which eventually corrected... I'm assuming you meant the same formula or you got another trick with you which for sure..will love to understand because it will be an array formula apart then this...

Saurabh...
-PolakAuthor Commented:
Cooooooooooooooooooolllll; there's no harm in making "ConstrainedReqs!$B$2:$B$13" ConstrainedReqs!$B:$B" right?
The size of this range will grow / shrink based on users selections.

This is great, out of curiosity..... since "AVERAGEPRODUCT" doesn't exist, would the easiest way to accomplish an average using the same functions above?
barry houdiniCommented:
Hey, Saurabh,

No I was referring to the version you posted, no better way IMO

For an average you can use an array formula with the same type of setup, e.g.

=AVERAGE(IF((AircraftData!$E$2:$E$21=AnalysisEngine!D3)*ISNUMBER(MATCH(AircraftData!$D$2:$D$21,ConstrainedReqs!$B$2:$B$13,0)),AircraftData!$H$2:$H$21))

confirmed with CTRL+SHIFT+ENTER

regards, barry
Saurabh Singh TeotiaCommented:
I won't use the entire column as a reference point as that will make the formula slow during calculation..Rather i would create a dynamic name range using offset which will automatically adjust itself basis of the data..

Also if you look average is Sum/count

So if you eliminate the last part of the formula of the sum..it will basically give you count..and you already have sum with you.. so this will do what you are looking for...

Saurabh...
Saurabh Singh TeotiaCommented:
Yeah barry i have been working on the isnumber for sometime and was messing up when giving input for match so i corrected and posted again...

Barry one thing i dont understand sumproduct is an array as well..but array normally takes more time sumproduct and its heavier..why does it take more time to calculate compare to sumproduct?
-PolakAuthor Commented:
Great Solution guys, Arrays are awesome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.