Index and Row

[Guest]

In the following, I am summing a horizontal range in an array that changes or
gets extended each month. The formula looks at this month's date and then
matches it to the correct column. Then it sums the row from the beginning
date to through the new month.

If I use the index formula as it is here, it does not increment the row
number when it gets copied down the sheet:

=IF(A35<=$O$3,+N35-SUM(U35:INDEX($U$4:BP172,1,MATCH($O$3,$U$4:BP$4,1) ,1)),0)

If I add Row(), it doesn't return the correct total.

If I add Row(##), it works only if I start the numbering 3 rows before the
actual row used.

=IF(A35<=$O$3,+N35-SUM(U35:INDEX($U$4:BP172,ROW(P32),MATCH($O$3,$U$4:
BP$4,1),1)),0)

In this last formula, the row is actually row 35, but I used row 32 and got
the correct total.

Does anyone know what's wrong with the formula? Thanks.

 

[Guest]

If I understand what you're attempting, this formula
=IF(A35<=$O$3,+N35-SUM(U35:INDEX($U$4:BP172,1,MATCH($O$3,$U$4:BP$4,1)
,1)),0) is intented to return the sum of some number of cells from row 35.
As I read it, however, the ending cell reference, created by the index
function, is always going to be in row 4 (the first row, nth column in the
array U4:BP172). I think you want this to be in row 35, so it would be
=IF(A35<=$O$3,+N35-SUM(U35:INDEX(U35:BP172,1,MATCH($O$3,$U$4:BP$4,1) ,1)),0)
That is, change the starting point of the array in the index function to the
current row, and make the reference relative so that it advances as you copy
the formula down. I think that's effectively what you did in the final
formula shown. By using row(p32) as the row number of the array beggining at
U4, you've got the same result as grabbing row 1 of the array starting at U35.
--Bruce

 

[Guest]

That simple change was the answer I needed. It works fine, now. Thank you.