sum in range meeting criteria

[geebee]

hi,

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


thanks in advance,
geebee

 

[Mike H]

Hi,

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

Try this

=SUMPRODUCT((A1:A8=A10)*(B18))

Mike

 

[geebee]

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

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.