Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
Can I use INDIRECT.EXT for this? | Excel Worksheet Functions | |||
Indirect | Excel Worksheet Functions | |||
INDIRECT !@%? | Excel Worksheet Functions | |||
Indirect Help | Excel Worksheet Functions |