I'm trying to sum a row if it meets two different criteria. Here's an
example. Here I would try and add column C if column A was 123 and column B
was group A. Or something similar. I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. Any ideas?

A B C
1 123 group A 5
2 456 group D 10
3 123 group B 14
4 456 group A 7
5 123 group C 6

Sumproduct does it for me

=SUMPRODUCT((A1:A5=123)*(B1:B5="Group A")*(C1:C5))

Mike

"mwear" wrote:

I'm trying to sum a row if it meets two different criteria. Here's an
example. Here I would try and add column C if column A was 123 and column B
was group A. Or something similar. I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. Any ideas?

A B C
1 123 group A 5
2 456 group D 10
3 123 group B 14
4 456 group A 7
5 123 group C 6

It's always useful to post the formula you tried. Have a go with this
one:

=SUMPRODUCT((A1:A5=123)*(B1:B5="groupA")*(C1:C5))

If the first column contains text values rather than real numbers then
you will have to put the 123 in quotes, like this - "123"

You can't use complete columns (unless you have XL 2007).

Hope this helps.

Pete

On Jan 18, 6:54*pm, mwear wrote:
I'm trying to sum a row if it meets two different criteria. *Here's an
example. *Here I would try and add column C if column A was 123 and column B
was group A. *Or something similar. *I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. *Any ideas?

* * * *A * * * * * *B * * * * * *C
1 * 123 * * * group A * * * 5
2 * 456 * * * group D * * *10
3 * 123 * * * group B * * *14
4 * 456 * * * group A * * * 7
5 * 123 * * * group C * * * 6

Can I do this across different worksheets? My column A, B, and C would be on
one worksheet and my references and answer would be another. Because, if I
copy the table and your formula it works, but when I transfer it to what I
need, it still comes up with an error.

Thanks for the quick response!!

"Mike H" wrote:

Sumproduct does it for me

=SUMPRODUCT((A1:A5=123)*(B1:B5="Group A")*(C1:C5))

Mike

"mwear" wrote:

I'm trying to sum a row if it meets two different criteria. Here's an
example. Here I would try and add column C if column A was 123 and column B
was group A. Or something similar. I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. Any ideas?

A B C
1 123 group A 5
2 456 group D 10
3 123 group B 14
4 456 group A 7
5 123 group C 6

Instead of actually putting = 123, can I put in = F6 or something. Where in
F6 I would have 123? That's how I actually have it.

Here is what I actually have:
SUMPRODUCT(('[08 Profit Summary.xls]1st Purchaser'!$A$2:$A$407=B22)*('[08
Profit Summary.xls]1st Purchaser'!$C$2:$C$407=C22)*'[08 Profit
Summary.xls]1st Purchaser'!$J:$J)

Thanks for the prompt response.



"Pete_UK" wrote:

It's always useful to post the formula you tried. Have a go with this
one:

=SUMPRODUCT((A1:A5=123)*(B1:B5="groupA")*(C1:C5))

If the first column contains text values rather than real numbers then
you will have to put the 123 in quotes, like this - "123"

You can't use complete columns (unless you have XL 2007).

Hope this helps.

Pete

On Jan 18, 6:54 pm, mwear wrote:
I'm trying to sum a row if it meets two different criteria. Here's an
example. Here I would try and add column C if column A was 123 and column B
was group A. Or something similar. I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. Any ideas?

A B C
1 123 group A 5
2 456 group D 10
3 123 group B 14
4 456 group A 7
5 123 group C 6

Try
=sum(if(a1:a5="123", if(b1:b5="group A", c1:c5, 0), 0)) with Ctrl+Shift+Enter

"mwear" wrote:

I'm trying to sum a row if it meets two different criteria. Here's an
example. Here I would try and add column C if column A was 123 and column B
was group A. Or something similar. I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. Any ideas?

A B C
1 123 group A 5
2 456 group D 10
3 123 group B 14
4 456 group A 7
5 123 group C 6

Yes you can do that. This formula can now go anywhere in the workbook and
look at values in sheet 3

=SUMPRODUCT((Sheet3!A1:A5=123)*(Sheet3!B1:B5="Grou p A")*(Sheet3!C1:C5))

Mike

"mwear" wrote:

Can I do this across different worksheets? My column A, B, and C would be on
one worksheet and my references and answer would be another. Because, if I
copy the table and your formula it works, but when I transfer it to what I
need, it still comes up with an error.

Thanks for the quick response!!

"Mike H" wrote:

Sumproduct does it for me

=SUMPRODUCT((A1:A5=123)*(B1:B5="Group A")*(C1:C5))

Mike

"mwear" wrote:

I'm trying to sum a row if it meets two different criteria. Here's an
example. Here I would try and add column C if column A was 123 and column B
was group A. Or something similar. I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. Any ideas?

A B C
1 123 group A 5
2 456 group D 10
3 123 group B 14
4 456 group A 7
5 123 group C 6

I realized what I did. I forgot to limit the J column.

Thank you so much for your help.

"Pete_UK" wrote:

It's always useful to post the formula you tried. Have a go with this
one:

=SUMPRODUCT((A1:A5=123)*(B1:B5="groupA")*(C1:C5))

If the first column contains text values rather than real numbers then
you will have to put the 123 in quotes, like this - "123"

You can't use complete columns (unless you have XL 2007).

Hope this helps.

Pete

On Jan 18, 6:54 pm, mwear wrote:
I'm trying to sum a row if it meets two different criteria. Here's an
example. Here I would try and add column C if column A was 123 and column B
was group A. Or something similar. I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. Any ideas?

A B C
1 123 group A 5
2 456 group D 10
3 123 group B 14
4 456 group A 7
5 123 group C 6

Ah, yes. I figured it out. I had forgotten to limit the sum column.

Thanks so much for your help.

"Mike H" wrote:

Sumproduct does it for me

=SUMPRODUCT((A1:A5=123)*(B1:B5="Group A")*(C1:C5))

Mike

"mwear" wrote:

I'm trying to sum a row if it meets two different criteria. Here's an
example. Here I would try and add column C if column A was 123 and column B
was group A. Or something similar. I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. Any ideas?

A B C
1 123 group A 5
2 456 group D 10
3 123 group B 14
4 456 group A 7
5 123 group C 6

You're welcome - glad you got it to work.

Pete

On Jan 18, 7:24*pm, mwear wrote:
I realized what I did. *I forgot to limit the J column.

Thank you so much for your help.



"Pete_UK" wrote:
It's always useful to post the formula you tried. Have a go with this
one:

=SUMPRODUCT((A1:A5=123)*(B1:B5="groupA")*(C1:C5))

If the first column contains text values rather than real numbers then
you will have to put the 123 in quotes, like this - "123"

You can't use complete columns (unless you have XL 2007).

Hope this helps.

Pete

On Jan 18, 6:54 pm, mwear wrote:
I'm trying to sum a row if it meets two different criteria. *Here's an
example. *Here I would try and add column C if column A was 123 and column B
was group A. *Or something similar. *I tried to used sumproduct (reading
other responses to similar questions) but I get #NUM! error. *Any ideas?

* * * *A * * * * * *B * * * * * *C
1 * 123 * * * group A * * * 5
2 * 456 * * * group D * * *10
3 * 123 * * * group B * * *14
4 * 456 * * * group A * * * 7
5 * 123 * * * group C * * * 6- Hide quoted text -

- Show quoted text -