Using SUM + INDEX in array formula
 

I'm having problems getting an array formula like this to work:
=SUM(INDEX(Data_Table,Row_Index_List,Col_Index_Lis t))

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

Using SUM + INDEX in array formula
 

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?

--
Best Regards,

Luke M
"Dave Ramage" wrote in message
...
I'm having problems getting an array formula like this to work:
=SUM(INDEX(Data_Table,Row_Index_List,Col_Index_Lis t))

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

Using SUM + INDEX in array formula
 

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

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

Using SUM + INDEX in array formula
 

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",Dat a_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_Lis t))

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

Using SUM + INDEX in array formula
 

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!




"Luke M" wrote:

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?

--
Best Regards,

Luke M
"Dave Ramage" wrote in message
...
I'm having problems getting an array formula like this to work:
=SUM(INDEX(Data_Table,Row_Index_List,Col_Index_Lis t))

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


.

Using SUM + INDEX in array formula
 

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",Dat a_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_Lis t))

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