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?

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?

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?