SUMIF with multiple criteria

[macquarl]

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.

[pinmaster]

Try:

=SUMPRODUCT((first_condition)*(second_condition),column to sum)

HTH
JG

[Bob Phillips]

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[macquarl]

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?

[Dave Peterson]

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