Adding numerical values based on multiple values in another column
 

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!

Adding numerical values based on multiple values in another column
 

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!

Adding numerical values based on multiple values in another column
 

=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!

Adding numerical values based on multiple values in another co
 

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!

Adding numerical values based on multiple values in another column
 

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!

Adding numerical values based on multiple values in another column
 

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!

Adding numerical values based on multiple values in another column
 

"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.