Prev: formula to return value from a matrix - I know hor and vert values
Next: formula to return value from a matrix - I know hor and vert va
From: Dave Ramage on 7 May 2010 04:19
Thanks for your time Jacob- this works just fine.

"Jacob Skaria" wrote:

> Dave, the earlier one work on the row number...If you have row/col "indexes"
> try the below version..
>
> =SUMPRODUCT(SUM(OFFSET(INDIRECT(CELL("address",Data_Table)),(Row_Index_List)-1,(Col_Index_List)-1)))
>
> --
> Jacob (MVP - Excel)
>
>
> "Jacob Skaria" wrote:
>
> > Hi Dave
> >
> > Try the below
> >
> > A1:A3 = row index
> > B1:B3 = col index
> >
> > Col A Col B
> > 1 4
> > 2 5
> > 3 6
> >
> > The below should sum up cells D1,E2 and F3...
> >
> > =SUMPRODUCT(SUM(OFFSET(A1,(A1:A3)-1,(B1:B3)-1)))
> >
> > --
> > Jacob (MVP - Excel)
> >
> >
> > "Dave Ramage" wrote:
> >
> > > I'm having problems getting an array formula like this to work:
> > > =SUM(INDEX(Data_Table,Row_Index_List,Col_Index_List))
> > >
> > > Basically, I have two columns of numbers that represent the row and column
> > > indexes from a data table (ranges Row_Index_List and Col_Index_List). I want
> > > to look up the numbers in the corresponding row/column of range Data_Table,
> > > and return the sum of all returned values.
> > >
> > > More detail: Row_Index_List and Col_Index_List are columns of (let's say)
> > > 100 cells, and Data_Table is a 7*5 range on the same sheet.
> > >
> > > It looks like the combination of SUM(INDEX(..)) does not work in an array
> > > formula. Can anyone suggest anything different. I have tried combinations of
> > > SUMIF, OFFSET, INDIRECT, SUMPRODUCT, but none seem to work. SUMIF gets close,
> > > but seems to return an array that is offset from the result I would expect.
> > >
> > > Thanks,
> > > Dave
First  |  Prev  | 
Pages: 1 2
Prev: formula to return value from a matrix - I know hor and vert values
Next: formula to return value from a matrix - I know hor and vert va