?
Solved

sum this

Posted on 2014-03-10
14
Medium Priority
?
242 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:Naresh Patel
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:Naresh Patel
Naresh Patel earned 400 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 34

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 34

Accepted Solution

by:
Rob Henson earned 1600 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:Naresh Patel
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:Naresh Patel
ID: 39917176
Mr.robhenson,

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


Thanks
0
 
LVL 34

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 34

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:Naresh Patel
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 34

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 34

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 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