From: Dave Ramage on
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
From: Luke M on
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" <DaveRamage(a)discussions.microsoft.com> wrote in message
news:D6BF9177-E2DE-4A52-AAA3-750E208F9116(a)microsoft.com...
> 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


From: Jacob Skaria on
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
From: Jacob Skaria on
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
From: Dave Ramage on
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" <DaveRamage(a)discussions.microsoft.com> wrote in message
> news:D6BF9177-E2DE-4A52-AAA3-750E208F9116(a)microsoft.com...
> > 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
>
>
> .
>