sum in range meeting criteria
 

hi,

i have a value in cell A10. i want to sum numbers in range B1:D8 where the
value in A10 value is equal to a value in range A1:A9. how can this be done?


thanks in advance,
geebee

sum in range meeting criteria
 

Hi,

I assume that A1:A9 is a typo and you mean A1:A8.

Try this

=SUMPRODUCT((A1:A8=A10)*(B1:D8))

Mike

"geebee" wrote:

hi,

i have a value in cell A10. i want to sum numbers in range B1:D8 where the
value in A10 value is equal to a value in range A1:A9. how can this be done?


thanks in advance,
geebee

sum in range meeting criteria
 

hi,

this formula works, but how do i copy it down for other rows in the column.
it looks like the other formulas i copying into the later rows are being
offset. i want it to keep the same range it looks up in... just look at the
value in A(rownumber).

thanks in advance,
geebee


"Mike H" wrote:

Hi,

I assume that A1:A9 is a typo and you mean A1:A8.

Try this

=SUMPRODUCT((A1:A8=A10)*(B1:D8))

Mike

"geebee" wrote:

hi,

i have a value in cell A10. i want to sum numbers in range B1:D8 where the
value in A10 value is equal to a value in range A1:A9. how can this be done?


thanks in advance,
geebee

sum in range meeting criteria
 

Use the Absolute reference to the range:
=SUMPRODUCT(($A$1:$A$8=$A10)*($B$1:$D$8))

The $ before one part of the address means it is an absolute reference and
will not change when copied. In the $A10 shown above, the $A column will
remain constant but the 10 (Row) will move + or - as you copy the formula up
or down. HTH.