Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
A user I am trying to help has a spread sheet that has several columns and he would like to sum one of the columns IF 2 other columns equal certain data If Column A = place ? AND Column C = ? then add Column D for a total ColA ColB ColC ColD Location Text Rating Points place 1 blah A 5 place 1 blah B 4 place 2 blah B 4 place 2 blah C 3 place 1 blah C 3 place 1 total A - formula to total all Place 1s and As (5) place 2 total B - formula to total all Place 1s and Bs (8) etc etc I'm sure I need to use SUMIF but I can't figure out how to involve multiple criteria to the formula. Any help would be greatly appreciated. -- macquarl ------------------------------------------------------------------------ macquarl's Profile: http://www.excelforum.com/member.php...o&userid=26388 View this thread: http://www.excelforum.com/showthread...hreadid=498115 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
Try: =SUMPRODUCT((first_condition)*(second_condition),c olumn to sum) HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=498115 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
=SUMPRODUCT(--(A2:A200="place 1"),==(C2:C200="B"),D2:D200)
as an example -- HTH Bob Phillips (remove nothere from email address if mailing direct) "macquarl" wrote in message ... A user I am trying to help has a spread sheet that has several columns and he would like to sum one of the columns IF 2 other columns equal certain data If Column A = place ? AND Column C = ? then add Column D for a total ColA ColB ColC ColD Location Text Rating Points place 1 blah A 5 place 1 blah B 4 place 2 blah B 4 place 2 blah C 3 place 1 blah C 3 place 1 total A - formula to total all Place 1s and As (5) place 2 total B - formula to total all Place 1s and Bs (8) etc etc I'm sure I need to use SUMIF but I can't figure out how to involve multiple criteria to the formula. Any help would be greatly appreciated. -- macquarl ------------------------------------------------------------------------ macquarl's Profile: http://www.excelforum.com/member.php...o&userid=26388 View this thread: http://www.excelforum.com/showthread...hreadid=498115 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
I attempted the SUMPRODUCT example and got "0" for my total when I should have gotten something. I am not sure if it's the right formula to use though, I need to add the points together for the bundle of locations and ranks. Isn't the SUMPRODUCT one used to multiply the different array elements together? -- macquarl ------------------------------------------------------------------------ macquarl's Profile: http://www.excelforum.com/member.php...o&userid=26388 View this thread: http://www.excelforum.com/showthread...hreadid=498115 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
With a minor correction to Bob's typing:
=SUMPRODUCT(--(A2:A200="place 1"),==(C2:C200="B"),D2:D200) becomes =SUMPRODUCT(--(A2:A200="place 1"),--(C2:C200="B"),D2:D200) You should have gotten a "good" answer. If 0 isn't correct, then I'd check A2:A200 to see if "place 1" is really in those cells (maybe extra spaces--leading, trailing, embedded. Same kind of thing with column C. And make sure d2:D200 are numbers--not text masquerading as numbers. if you type: =count(d2:D200) do you get 199 (assuming no empty cells) macquarl wrote: I attempted the SUMPRODUCT example and got "0" for my total when I should have gotten something. I am not sure if it's the right formula to use though, I need to add the points together for the bundle of locations and ranks. Isn't the SUMPRODUCT one used to multiply the different array elements together? -- macquarl ------------------------------------------------------------------------ macquarl's Profile: http://www.excelforum.com/member.php...o&userid=26388 View this thread: http://www.excelforum.com/showthread...hreadid=498115 -- Dave Peterson |
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 | |||
Multiple SUMIF Criteria | Excel Worksheet Functions | |||
Can I use multiple criteria in SUMIF funqtion? | Excel Worksheet Functions | |||
How do I sum (like sumif) but predicated on multiple criteria, in. | Excel Worksheet Functions | |||
How do I ask for multiple criteria when creating a "sumif" formul. | Excel Worksheet Functions |