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

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

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

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

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