SUMIF with multi-column sum_range
 

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

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
wrote:

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

Myrna

Thank you, the SUMPRODUCT solution is perfect.



"Myrna Larson" wrote:

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
wrote:

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