Using SUM + INDEX in array formula

[Dave Ramage]

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

 

[Luke M]

To clarify, you're essentially wanting to perform 100 INDEX functions based
on the two columns, and SUM all the results?

My first question would be, why can't you just use a helper column to
perform this step, and then SUM that?

 

[Jacob Skaria]

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

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)))

 

[Dave Ramage]

Luke- agreed, a helper column would be the easy option, but I always like to
do a calculation in one cell if at all possible- after all, an extra column
is just an array of values, so why can't this be included in a single cell
formula.

Obviously the formula gets more complicated and more difficult for others to
modify later, but for me the advantage of neater data presentation without
having to hide columns is more important.

Thanks to Jacob's reply, it is possible!

 

[Dave Ramage]

Thanks for your time Jacob- this works just fine.