| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index Array Formula Assistance | Excel Worksheet Functions | |||
| Array formula on INDEX function not working | Excel Worksheet Functions | |||
| Array formula with index + match | Excel Worksheet Functions | |||
| Index and Match Array formula | Excel Worksheet Functions | |||
| How to add in an array formula if iisna index match | Excel Worksheet Functions | |||
Threaded Mode
