Easier Match/Offset?
Or, normally entered (not an array):
=SUMPRODUCT(I21:L21*OFFSET($I$6,MATCH(A12,$H$1:$H$ 8,0)-6,0))
Biff
"Biff" wrote in message
...
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):
=SUM(I21:L21*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))
Biff
"JavyD" wrote in message
...
Is there an easier way to do this?
Basicly the Match is looking at a specific field in A12, which needs to
be
dynamic as I'm pasting this formula down. Once the field matches, it
will
ofset it based on its Cell Row #-6 rows, then I need it to calculate I21
*the
matching row/cell reference. I21 needs to offset based on column. It
looks
like a mess, but seems to be doing the job. But I'm sure others in the
office may get lost, was wondering if there was somethign easier to do.
=(OFFSET(I21,0,0)*OFFSET($I$6,MATCH(A12,$H$1:$H$8, 0)-6,0))+(OFFSET(I21,0,1)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,0))+(OFFSET(I21,0,2)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,0))+(OFFSET(I21,0,3)*OFFSET($I$6,MATCH(A12,$H$1: $H$8,0)-6,0))
|