D
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
=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