Solved

# sum this

Posted on 2014-03-10
234 Views
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
0
Question by:Rayne
• 6
• 5
• 3

LVL 8

Expert Comment

ID: 39916968
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

LVL 8

Assisted Solution

itjockey earned 100 total points
ID: 39916969
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

LVL 32

Expert Comment

ID: 39917122
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

LVL 32

Accepted Solution

Rob Henson earned 400 total points
ID: 39917139
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

LVL 8

Expert Comment

ID: 39917143
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

LVL 8

Expert Comment

ID: 39917176
Mr.robhenson,

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

Thanks
0

LVL 32

Expert Comment

ID: 39917274
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

LVL 32

Expert Comment

ID: 39917289
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

LVL 8

Expert Comment

ID: 39917322
Mr.robhenson,

Thank You Very Much For Explanation :)
0

Author Comment

ID: 39918193
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

LVL 32

Expert Comment

ID: 39918761
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 Comment

ID: 39918803
Not a problem Rob, all I can say is You ROCK!!

:) thank you, it works perfect
0

LVL 32

Expert Comment

ID: 39918822
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 Comment

ID: 39919538
awesome, thats what i was looking for  - flexibility - perfect :) - yes i had the sequencing issue in mind
Thank you Rob
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

#### 810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.