Please help
I am trying to add numerical values in one column based on multiple values
in another column.

A B
Red $24
Blue $16
Green $21
Blue $15
Green $12

I would like to add the values in column B for all rows that have a value of
red and green in column A. Thank you in advance for your help!

A couple of ways

=SUM(SUMIF(A2:A6,{"Red";"Green"},B2:B6))

or

=SUMPRODUCT(--((A2:A6="Red")+(A2:A6="Green")0),B2:B6)

both returns 57


--
Regards,

Peo Sjoblom



"Kazmaniac" wrote in message
...
Please help
I am trying to add numerical values in one column based on multiple values
in another column.

A B
Red $24
Blue $16
Green $21
Blue $15
Green $12

I would like to add the values in column B for all rows that have a value
of
red and green in column A. Thank you in advance for your help!

=SUMPRODUCT((A2:A6={"Red","Green"})*B2:B6)

"Kazmaniac" wrote:

Please help
I am trying to add numerical values in one column based on multiple values
in another column.

A B
Red $24
Blue $16
Green $21
Blue $15
Green $12

I would like to add the values in column B for all rows that have a value of
red and green in column A. Thank you in advance for your help!

THANK YOU!!!!! WORKS LIKE A CHARM!!!

"Peo Sjoblom" wrote:

A couple of ways

=SUM(SUMIF(A2:A6,{"Red";"Green"},B2:B6))

or

=SUMPRODUCT(--((A2:A6="Red")+(A2:A6="Green")0),B2:B6)

both returns 57


--
Regards,

Peo Sjoblom



"Kazmaniac" wrote in message
...
Please help
I am trying to add numerical values in one column based on multiple values
in another column.

A B
Red $24
Blue $16
Green $21
Blue $15
Green $12

I would like to add the values in column B for all rows that have a value
of
red and green in column A. Thank you in advance for your help!

Why not just
=SUMPRODUCT((A1:A5="red")+(A1:A5="green"),B1:B5)
No need for "- -" since the addition operator will coerce logical values to
numeric
No need for "0" since 0 * anything = 0
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Peo Sjoblom" wrote in message
...
A couple of ways

=SUM(SUMIF(A2:A6,{"Red";"Green"},B2:B6))

or

=SUMPRODUCT(--((A2:A6="Red")+(A2:A6="Green")0),B2:B6)

both returns 57


--
Regards,

Peo Sjoblom



"Kazmaniac" wrote in message
...
Please help
I am trying to add numerical values in one column based on multiple
values
in another column.

A B
Red $24
Blue $16
Green $21
Blue $15
Green $12

I would like to add the values in column B for all rows that have a value
of
red and green in column A. Thank you in advance for your help!

The reason I use it is because I want to be fairly consistent, if for
instance the OP had asked Green OR Red in A2:B5 then sum C2:C5. If for
instance A2 holds red and B2 holds green your formula would do 2 x C2 while
mine would do
1 x C2


--
Regards,

Peo Sjoblom


"Bernard Liengme" wrote in message
...
Why not just
=SUMPRODUCT((A1:A5="red")+(A1:A5="green"),B1:B5)
No need for "- -" since the addition operator will coerce logical values
to numeric
No need for "0" since 0 * anything = 0
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Peo Sjoblom" wrote in message
...
A couple of ways

=SUM(SUMIF(A2:A6,{"Red";"Green"},B2:B6))

or

=SUMPRODUCT(--((A2:A6="Red")+(A2:A6="Green")0),B2:B6)

both returns 57


--
Regards,

Peo Sjoblom



"Kazmaniac" wrote in message
...
Please help
I am trying to add numerical values in one column based on multiple
values
in another column.

A B
Red $24
Blue $16
Green $21
Blue $15
Green $12

I would like to add the values in column B for all rows that have a
value of
red and green in column A. Thank you in advance for your help!

"Bernard Liengme" wrote...
Why not just
=SUMPRODUCT((A1:A5="red")+(A1:A5="green"),B1:B5 )
No need for "- -" since the addition operator will coerce logical
values to numeric
....

For another reason,

=SUM(SUMIF(A1:A5,{"red";"green"},B1:B5))

is more efficient both in terms of storage and recalc speed. And it's
more general (not that this is an issue in this instance), i.e.,

=SUM(SUMIF(A:A,{"red";"green"},B:B))

works, while

=SUMPRODUCT((A:A="red")+(A:A="green"),B:B)

will fail in XL2003 and prior versions.