SUMIF with multi-column sum_range

K

Kevin B

When using SUMIF, is the [sum_range] restricted to values in a single column?
To explain my question I have the following data:

ADD LIST A LIST B
Y 120 0
85 217
Y 0 50
Y 40 92

When I insert the formula "=SUMIF($A$2:$A$5,"Y",$B$2:$C$5)" I am getting the
value 160 because the sum_range of $B$2:$C$5 is returning only the sum of
values in column B. What I would like is to see a value of 302.

Is it possible for the [sum_range] to be a range across multiple contiguous
columns?

Thanks in advance
 
M

Myrna Larson

You said it doesn't work, so evidently the answer to your question is NO.

You can use 2 SUMIF FORMULAs, i.e.

=SUMIF($A$2:$A$5,"Y",$B$2:$B$5)+SUMIF($A$2:$A$5,"Y",$C$2:$C$5)

or

=SUMPRODUCT(($A$2:$A$5="Y")*($B$2:$C$5))

On Tue, 16 Nov 2004 17:23:02 -0800, "Kevin B" <Kevin
 
K

Kevin B

Myrna

Thank you, the SUMPRODUCT solution is perfect.



Myrna Larson said:
You said it doesn't work, so evidently the answer to your question is NO.

You can use 2 SUMIF FORMULAs, i.e.

=SUMIF($A$2:$A$5,"Y",$B$2:$B$5)+SUMIF($A$2:$A$5,"Y",$C$2:$C$5)

or

=SUMPRODUCT(($A$2:$A$5="Y")*($B$2:$C$5))

On Tue, 16 Nov 2004 17:23:02 -0800, "Kevin B" <Kevin
When using SUMIF, is the [sum_range] restricted to values in a single column?
To explain my question I have the following data:

ADD LIST A LIST B
Y 120 0
85 217
Y 0 50
Y 40 92

When I insert the formula "=SUMIF($A$2:$A$5,"Y",$B$2:$C$5)" I am getting the
value 160 because the sum_range of $B$2:$C$5 is returning only the sum of
values in column B. What I would like is to see a value of 302.

Is it possible for the [sum_range] to be a range across multiple contiguous
columns?

Thanks in advance
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top