Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF with Mutiple Ranges & Criteria
I'm doing a sales report and have a single sheet with all my call/result metrics on it. I want to use SUMIF (I think ;) ) to calculate the cells in a particular column if a cell in a specific column has a specific text entry as well as a different text entry in another column (the two columns are region and district). I know how do do a single range/criteria in a SUMIF statement, but I don't know how to add multiple SUMIF range/criterias. So basically here is what I am looking for: Sum a particular column G if column F equals "East Enterprise" and column V equals "CSI". I am currently breaking these out in a pivot table and adding a seperate sheet to the report, then doing a sumif statement on the columns I need but I know there has to be a way to do it in a single formula, it is just beyond my grasp. Also, if this makes no sense whatsoever, FLAME away... that just may be the movitation I need today. Humbly, PokerZan -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=392980 |
#2
|
|||
|
|||
=SUMPRODUCT(--(F2:F100="East Enterprise"),--(V2:v100="CSI"),G2:G100)
-- HTH RP (remove nothere from the email address if mailing direct) "PokerZan" wrote in message ... I'm doing a sales report and have a single sheet with all my call/result metrics on it. I want to use SUMIF (I think ;) ) to calculate the cells in a particular column if a cell in a specific column has a specific text entry as well as a different text entry in another column (the two columns are region and district). I know how do do a single range/criteria in a SUMIF statement, but I don't know how to add multiple SUMIF range/criterias. So basically here is what I am looking for: Sum a particular column G if column F equals "East Enterprise" and column V equals "CSI". I am currently breaking these out in a pivot table and adding a seperate sheet to the report, then doing a sumif statement on the columns I need but I know there has to be a way to do it in a single formula, it is just beyond my grasp. Also, if this makes no sense whatsoever, FLAME away... that just may be the movitation I need today. Humbly, PokerZan -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=392980 |
#3
|
|||
|
|||
Thanks Bob, It did the trick! One quick question though, what do the "--" do before the parentheses do? PZan -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=392980 |
#4
|
|||
|
|||
And the questions never stop... :P There are about 7 different options that could be in column V, I am only looking to extract "CSI", the others I want to add up the other 6 options... So is there an way to add up all that DON'T say "CSI" in column V? Thanks, PZan -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=392980 |
#5
|
|||
|
|||
Yeah, straight-forward
=SUMPRODUCT(--(F2:F100="East Enterprise"),--(V2:v100<"CSI"),G2:G100) for the low-down on --, take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH RP (remove nothere from the email address if mailing direct) "PokerZan" wrote in message ... And the questions never stop... :P There are about 7 different options that could be in column V, I am only looking to extract "CSI", the others I want to add up the other 6 options... So is there an way to add up all that DON'T say "CSI" in column V? Thanks, PZan -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=392980 |
#6
|
|||
|
|||
Wow, great site, thanks a ton! -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480 View this thread: http://www.excelforum.com/showthread...hreadid=392980 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
SUMIF using VLOOKUP as criteria | Excel Worksheet Functions | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
SUMIF and 3-D Ranges | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |