Using SUM + INDEX in array formula
in [Functions]
|
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 6 May 2010 09:51 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 6 May 2010 10:31 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 6 May 2010 11:04 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 6 May 2010 11:12 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 7 May 2010 04:18
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 > > > . > |