Solved

sum this

Posted on 2014-03-10
14
229 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 31

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
 
LVL 31

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 31

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 31

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 31

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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now