Solved

sum this

Posted on 2014-03-10
14
232 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 32

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 32

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 32

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

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 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA - Returning results from batch file 9 66
Split a string in excel 9 30
Update As Well As Add 6 37
Pivot help - Display only Is Not Null 7 15
My experience with Windows 10 over a one year period and suggestions for smooth operation
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

912 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

23 Experts available now in Live!

Get 1:1 Help Now