sum this

Hello To all Gurus,

I am looking for sum prodct formula - there will be 4 drop downs that will determine the 4 criteria set for the sum product – then based of the 4 selections  - the formula sums up the sales –
For example – if users select on the country dropdown  = CN and no other drop down  - it sums up the sales for CN. If user selects a country and a state, then it sums up the sales for that combination

Thank you
EE-Ques-3-10.xlsx
RayneAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
So in your example:

=SUMIFS(Source1!$I:$I,Source1!$B:$B,IF($B2="","*",$B2),Source1!$D:$D,IF($B3="","*",$B3),Source1!$C:$C,IF($B4="","*",$B4),Source1!$E:$E,IF($B5="","*",$B5))

Thanks
Rob H
0
 
Naresh PatelTraderCommented:
Try this
=IF(COUNTA(B2:B5)=1,SUMIF(Source1!B2:B44,mainPage!B2,Source1!I2:I44),IF(COUNTA(B2:B5)=2,SUMIFS(Source1!I2:I44,Source1!B2:B44,mainPage!B2,Source1!D2:D44,mainPage!B3),IF(COUNTA(B2:B5)=3,SUMIFS(Source1!I2:I44,Source1!B2:B44,mainPage!B2,Source1!D2:D44,mainPage!B3,Source1!E2:E44,mainPage!B5),"")))

Open in new window

0
 
Naresh PatelConnect With a Mentor TraderCommented:
I don't get Cell B4 drop down values in Source1 Sheet. pls clarify.



Thanks
=IF(COUNTA(B2:B5)=1,SUMIF(Source1!B2:B44,mainPage!B2,Source1!I2:I44),IF(COUNTA(B2:B5)=2,SUMIFS(Source1!I2:I44,Source1!B2:B44,mainPage!B2,Source1!D2:D44,mainPage!B3),IF(COUNTA(B2:B5)=3,SUMIFS(Source1!I2:I44,Source1!B2:B44,mainPage!B2,Source1!D2:D44,mainPage!B3,Source1!E2:E44,mainPage!B5),"")))

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
You can use SUMIFS function with Wild cards in IF statements for the blanks:

=SUMIFS(SumRange,Criteria1Range,IF(Criteria1="","*",Criteria1),Criteria2Range,IF(Criteria2="","*",Criteria2),Criteria3Range,IF(Criteria3="","*",Criteria3),Criteria4Range,IF(Criteria4="","*",Criteria4))

I can't see particularly well when posting, you may see better once posted, the second statement in the IF functions is an asterisk within double quotes.

Thanks
Rob H
0
 
Naresh PatelTraderCommented:
Sorry forgot to attached sample WB. & my bad regarding my previous post now all set just check WB.

Thanks
EE-Ques-3-10.xlsx
0
 
Naresh PatelTraderCommented:
Mr.robhenson,

Your formula seems perfect for me can explain logic behind this? "Wild Cards"....?


Thanks
0
 
Rob HensonFinance AnalystCommented:
The SUMIFS function allows users the ability to sum a range based on a combination of multiple criteria. The result is where ALL criteria are met, the equivalent to an AND statement, if that helps.

In your question you specify that there are 4 possible criteria but you may not set them all. So by specifying * as one or more criteria, it will effectively ignore that criteria because the * tells the criteria to be "anything" or "All" so the logic in the IF statement is saying:

IF the particular criteria is blank use * (anything) otherwise use the criteria set.

Hope that helps, or is it the phrase "Wild Cards" that you don't understand?

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
To break out the actual formula if it makes it clearer:

=SUMIFS(Source1!$I:$I,  - Add up column I on Source1

Source1!$B:$B,IF($B2="","*",$B2), - Where Source1 Column B is same as cell B2 or anything in column B if B2 is blank

Source1!$D:$D,IF($B3="","*",$B3), - AND Where Source1 Column D is same as cell B3 or anything in column D if B3 is blank

Source1!$C:$C,IF($B4="","*",$B4), - AND Where Source1 Column C is same as cell B4 or anything in column C if B4 is blank

Source1!$E:$E,IF($B5="","*",$B5)) - AND Where Source1 Column E is same as cell B5 or anything in column E if B5 is blank

Thanks
Rob H
0
 
Naresh PatelTraderCommented:
Mr.robhenson,

Thank You Very Much For Explanation :)
0
 
RayneAuthor Commented:
Hello All,

thank you all for your help so far - is there something in sumproduct that can do this as well....like it returns 1 when the once the criteria is one
0
 
Rob HensonFinance AnalystCommented:
I had a try with SUMPRODUCT as well but couldn't get it to work. The only advantage that I can think of would be compatibility with earlier versions. Would that be an issue?

Rob
0
 
RayneAuthor Commented:
Not a problem Rob, all I can say is You ROCK!!

:) thank you, it works perfect
0
 
Rob HensonFinance AnalystCommented:
The only caveat that I will add, but it could be advantage, the sum criteria are all exclusive. In other words, you can put any combination of the 4 choices to get the sums. They don't have to be in order such as 1 & 2 or 1 2 & 3. Actually all blank will workabd end up with sum of total.

thanks
Rob
0
 
RayneAuthor Commented:
awesome, thats what i was looking for  - flexibility - perfect :) - yes i had the sequencing issue in mind
Thank you Rob
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.

All Courses

From novice to tech pro — start learning today.