|
Indirect Sum
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. |
Indirect Sum
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... -- HTH... Jim Thomlinson "Micki" wrote: 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. |
Indirect Sum
You are fast! Thanks. It worked.
"Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Micki" wrote: 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. |
| All times are GMT +1. The time now is 02:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com