SUMPRODUCT
 

The following formula in cell I8 produces the correct result for the single
cell (I12):
=PRODUCT(I12,VLOOKUP(OFFSET(I12,0,-8),'Customer Quote'!$A$16:$G$65,7))
However, I would like to enhance this formula to sum the results of the
PRODUCT of every cell within the range I12:I50. I suspect that it may be an
array formula but cannot see the solution.
Can anybody help me please?

SUMPRODUCT
 

Ron,

VLOOKUP is not array formula "friendly", so your best bet is to copy the formula down to match your
range, and SUM the formulas.

HTH,
Bernie
MS Excel MVP


"Ron@Buy" wrote in message
...
The following formula in cell I8 produces the correct result for the single
cell (I12):
=PRODUCT(I12,VLOOKUP(OFFSET(I12,0,-8),'Customer Quote'!$A$16:$G$65,7))
However, I would like to enhance this formula to sum the results of the
PRODUCT of every cell within the range I12:I50. I suspect that it may be an
array formula but cannot see the solution.
Can anybody help me please?

SUMPRODUCT
 

Thanks Bernie I suspected as much!


"Bernie Deitrick" wrote:

Ron,

VLOOKUP is not array formula "friendly", so your best bet is to copy the formula down to match your
range, and SUM the formulas.

HTH,
Bernie
MS Excel MVP


"Ron@Buy" wrote in message
...
The following formula in cell I8 produces the correct result for the single
cell (I12):
=PRODUCT(I12,VLOOKUP(OFFSET(I12,0,-8),'Customer Quote'!$A$16:$G$65,7))
However, I would like to enhance this formula to sum the results of the
PRODUCT of every cell within the range I12:I50. I suspect that it may be an
array formula but cannot see the solution.
Can anybody help me please?