# 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
###### Who is Participating?

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

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),"")))
``````
0

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),"")))
``````
0

Finance 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

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

Mr.robhenson,

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

Thanks
0

Finance 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

Finance 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

Mr.robhenson,

Thank You Very Much For Explanation :)
0

Author 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

Finance 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

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

:) thank you, it works perfect
0

Finance 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

Author 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.