Indirect Sum

[Guest]

I want to sum a row of values but the columns will vary each month. The start
point of each row will always be column V, but the ending column will expand
by one column each month. I have entered the formula
=SUM(INDIRECT("v11:"&$B$2&"11")), which works, but I need to copy this down
to many rows. How can I write it so the row numbers will increase by 1 as I
copy down to subsequent rows so that it reads to sum v12, v13 etc.

 

[Guest]

You can use something like this...

=SUM(INDIRECT("V" & ROW() & ":" & $B$2 & ROW()))

Note that this is a volatile function so that it will have a detreemental
effect on your calculation performance as it will be recalculated every time
a calculation is run. You are ok if there are not too many but if you have
thousands of these formulas you will take a performance hit...

 

[Guest]

You are fast! Thanks. It worked.

You can use something like this...

=SUM(INDIRECT("V" & ROW() & ":" & $B$2 & ROW()))

Note that this is a volatile function so that it will have a detreemental
effect on your calculation performance as it will be recalculated every time
a calculation is run. You are ok if there are not too many but if you have
thousands of these formulas you will take a performance hit...