| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of ROW() in array formula
I want to increment a value used an array formula when it is copied to
the cell below it. In a non-array formula I'd use ROW(A1) but in the following array formula it causes an error: =SUM((OFFSET(RefIDs,0,0,1)<"P")*(OFFSET(Balances, 0,0,1))) In case it isn't obvious, RefIDs and Balances are defined names that each refer to a range. Say this is entered in cell H9. When I copy it down to cell H10 I want it to be: =SUM((OFFSET(RefIDs,1,0,1)<"P")*(OFFSET(Balances, 1,0,1))) Here is my attempt that doesn't work: =SUM((OFFSET(RefIDs,ROW(A1)-1,0,1)<"P")*(OFFSET(Balances,ROW(A1)-1,0,1))) Can anyone point me in the direction of the solution, please? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use of ROW() in array formula
Your formula looks like it should be
=SumProduct(... or maybe you want =SumIf(... (not sure, but...) -- sb "onedaywhen" wrote in message om... I want to increment a value used an array formula when it is copied to the cell below it. In a non-array formula I'd use ROW(A1) but in the following array formula it causes an error: =SUM((OFFSET(RefIDs,0,0,1)<"P")*(OFFSET(Balances, 0,0,1))) In case it isn't obvious, RefIDs and Balances are defined names that each refer to a range. Say this is entered in cell H9. When I copy it down to cell H10 I want it to be: =SUM((OFFSET(RefIDs,1,0,1)<"P")*(OFFSET(Balances, 1,0,1))) Here is my attempt that doesn't work: =SUM((OFFSET(RefIDs,ROW(A1)-1,0,1)<"P")*(OFFSET(Balances,ROW(A1)-1,0,1))) Can anyone point me in the direction of the solution, please? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use of ROW() in array formula
Thanks, Steve.
I worked out that I could use ROWS rather than ROW i.e. =SUM((OFFSET(RefIDs,ROWS(A$1:A1)-1,0,1)<"P")*(OFFSET(Balances,ROWS(A$1:A1)-1,0,1))) "steve" wrote in message ... Your formula looks like it should be =SumProduct(... or maybe you want =SumIf(... (not sure, but...) -- sb "onedaywhen" wrote in message om... I want to increment a value used an array formula when it is copied to the cell below it. In a non-array formula I'd use ROW(A1) but in the following array formula it causes an error: =SUM((OFFSET(RefIDs,0,0,1)<"P")*(OFFSET(Balances, 0,0,1))) In case it isn't obvious, RefIDs and Balances are defined names that each refer to a range. Say this is entered in cell H9. When I copy it down to cell H10 I want it to be: =SUM((OFFSET(RefIDs,1,0,1)<"P")*(OFFSET(Balances, 1,0,1))) Here is my attempt that doesn't work: =SUM((OFFSET(RefIDs,ROW(A1)-1,0,1)<"P")*(OFFSET(Balances,ROW(A1)-1,0,1))) Can anyone point me in the direction of the solution, please? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
| Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
| Find specific value in array of array formula | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Linear Mode
