Solved

sum this

Posted on 2014-03-10
14
235 Views
Last Modified: 2014-03-10
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
Comment
Question by:Rayne
  • 6
  • 5
  • 3
14 Comments
 
LVL 8

Expert Comment

by:itjockey
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),"")))

Open in new window

0
 
LVL 8

Assisted Solution

by:itjockey
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),"")))

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Accepted Solution

by:
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

by:itjockey
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

by:itjockey
ID: 39917176
Mr.robhenson,

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


Thanks
0
 
LVL 33

Expert Comment

by:Rob Henson
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 33

Expert Comment

by:Rob Henson
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

by:itjockey
ID: 39917322
Mr.robhenson,

Thank You Very Much For Explanation :)
0
 

Author Comment

by:Rayne
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 33

Expert Comment

by:Rob Henson
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

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

:) thank you, it works perfect
0
 
LVL 33

Expert Comment

by:Rob Henson
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

by:Rayne
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

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

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

Join & Ask a Question