Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing dynamic range
Let imagine this situation:
I have column with set of ranges and I need to sumarise every particular range. for example: D4: 9 D5: 5 D6: 3 D7: =SUM(D4:D6) D8: D9: 1 D10: 2 D11: 3 D12: =SUM(D9:D11) D13: D14: 1 D15: 1 D16: 1 D17: 1 D18: 1 D19: 1 D20: 1 D21: =SUM(D14:D20) etc .... My Idea is have in every "Sum cell" the same formula. Every single range can vary in mumber of rows. In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in sheet formulas ? Thanks in advance to all ! Vlado |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing dynamic range
How do you identify which rows to sum?
"Vlado Sveda" wrote: Let imagine this situation: I have column with set of ranges and I need to sumarise every particular range. for example: D4: 9 D5: 5 D6: 3 D7: =SUM(D4:D6) D8: D9: 1 D10: 2 D11: 3 D12: =SUM(D9:D11) D13: D14: 1 D15: 1 D16: 1 D17: 1 D18: 1 D19: 1 D20: 1 D21: =SUM(D14:D20) etc .... My Idea is have in every "Sum cell" the same formula. Every single range can vary in mumber of rows. In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in sheet formulas ? Thanks in advance to all ! Vlado |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing dynamic range
That's the problem I need to solve. I need to sum all cells above the "Sum
cell" in continuous range - as described in my example. "bj" wrote: How do you identify which rows to sum? "Vlado Sveda" wrote: Let imagine this situation: I have column with set of ranges and I need to sumarise every particular range. for example: D4: 9 D5: 5 D6: 3 D7: =SUM(D4:D6) D8: D9: 1 D10: 2 D11: 3 D12: =SUM(D9:D11) D13: D14: 1 D15: 1 D16: 1 D17: 1 D18: 1 D19: 1 D20: 1 D21: =SUM(D14:D20) etc .... My Idea is have in every "Sum cell" the same formula. Every single range can vary in mumber of rows. In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in sheet formulas ? Thanks in advance to all ! Vlado |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing dynamic range
Vlado,
The following is not the most elegant solution I can think of, but it works, provided that there are still blank cells between the blocks, after your summation formula is inserted. The following *array* formula is placed directly below a contiguous group of cells in column A, with data starting from A1: =SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(MAX(IF(A $1:A12="",ROW(A$1:A12)))+1,COLUMN()))) As it is an array formula, commit with Shift+Ctrl+Enter. HTH Kostis Vezerides On Oct 11, 2:03 pm, Vlado Sveda wrote: Let imagine this situation: I have column with set of ranges and I need to sumarise every particular range. for example: D4: 9 D5: 5 D6: 3 D7: =SUM(D4:D6) D8: D9: 1 D10: 2 D11: 3 D12: =SUM(D9:D11) D13: D14: 1 D15: 1 D16: 1 D17: 1 D18: 1 D19: 1 D20: 1 D21: =SUM(D14:D20) etc .... My Idea is have in every "Sum cell" the same formula. Every single range can vary in mumber of rows. In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in sheet formulas ? Thanks in advance to all ! Vlado |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing dynamic range
Hi
With your values to be summed in column D, enter in E2 =IF(D2<"","",SUM($D$1:D1)-SUM($E$1:E1)) copy down column E as far as required -- Regards Roger Govier "Vlado Sveda" wrote in message ... That's the problem I need to solve. I need to sum all cells above the "Sum cell" in continuous range - as described in my example. "bj" wrote: How do you identify which rows to sum? "Vlado Sveda" wrote: Let imagine this situation: I have column with set of ranges and I need to sumarise every particular range. for example: D4: 9 D5: 5 D6: 3 D7: =SUM(D4:D6) D8: D9: 1 D10: 2 D11: 3 D12: =SUM(D9:D11) D13: D14: 1 D15: 1 D16: 1 D17: 1 D18: 1 D19: 1 D20: 1 D21: =SUM(D14:D20) etc .... My Idea is have in every "Sum cell" the same formula. Every single range can vary in mumber of rows. In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in sheet formulas ? Thanks in advance to all ! Vlado |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing dynamic range
select the cell you want the sum to be in go up to your workbar and pust the
button with the sigma on it. I "think" it will do exactly what you want. "Vlado Sveda" wrote: That's the problem I need to solve. I need to sum all cells above the "Sum cell" in continuous range - as described in my example. "bj" wrote: How do you identify which rows to sum? "Vlado Sveda" wrote: Let imagine this situation: I have column with set of ranges and I need to sumarise every particular range. for example: D4: 9 D5: 5 D6: 3 D7: =SUM(D4:D6) D8: D9: 1 D10: 2 D11: 3 D12: =SUM(D9:D11) D13: D14: 1 D15: 1 D16: 1 D17: 1 D18: 1 D19: 1 D20: 1 D21: =SUM(D14:D20) etc .... My Idea is have in every "Sum cell" the same formula. Every single range can vary in mumber of rows. In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in sheet formulas ? Thanks in advance to all ! Vlado |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing dynamic range
Hi Kostis, your code works fine, but in my case I don't know starting and ending cell of range that I want summarise. I need substitute A$1:A12 with "something flowing". In my example (see bellow) I need SUM in D7, SUM in D12, SUM in D21, ... but these addresses are "flowing" because those ranges (D4:D6, D9:D11, D14:D20, ...) are dynamic in their sizes. These ranges could be D4:D20, D23:D24, D27:D1234, ... or whatever else ... and all these ranges are in one column. So that is the reason I need "summing dynamic ranges". Regards Vlado "vezerid" wrote: Vlado, The following is not the most elegant solution I can think of, but it works, provided that there are still blank cells between the blocks, after your summation formula is inserted. The following *array* formula is placed directly below a contiguous group of cells in column A, with data starting from A1: =SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(MAX(IF(A $1:A12="",ROW(A$1:A12)))+1,COLUMN()))) As it is an array formula, commit with Shift+Ctrl+Enter. HTH Kostis Vezerides On Oct 11, 2:03 pm, Vlado Sveda wrote: Let imagine this situation: I have column with set of ranges and I need to sumarise every particular range. for example: D4: 9 D5: 5 D6: 3 D7: =SUM(D4:D6) D8: D9: 1 D10: 2 D11: 3 D12: =SUM(D9:D11) D13: D14: 1 D15: 1 D16: 1 D17: 1 D18: 1 D19: 1 D20: 1 D21: =SUM(D14:D20) etc .... My Idea is have in every "Sum cell" the same formula. Every single range can vary in mumber of rows. In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in sheet formulas ? Thanks in advance to all ! Vlado |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing dynamic range
Vlado,
First of all, I used A$1:Ax so that the formula can be copied to any column and always look from the top cell of that column. Also, the formulas are indeed "dynamic" in that, once placed below a contiguous block of cells, they will produce the sum of the block. Originally I thought you already had the numbers and were seeking formulas to insert in the blanks. I am a bit perplexed after your answer. How are these numbers coming, from some VBA or query? If we have a column which is always changing then you might need a VBA solution, to implant the sum automatically under each block. Please try to be more specific as to your requirements or email me a copy of your file with annotations. Regards, Kostis On Oct 12, 9:31 am, Vlado Sveda wrote: Hi Kostis, your code works fine, but in my case I don't know starting and ending cell of range that I want summarise. I need substitute A$1:A12 with "something flowing". In my example (see bellow) I need SUM in D7, SUM in D12, SUM in D21, ... but these addresses are "flowing" because those ranges (D4:D6, D9:D11, D14:D20, ...) are dynamic in their sizes. These ranges could be D4:D20, D23:D24, D27:D1234, ... or whatever else ... and all these ranges are in one column. So that is the reason I need "summing dynamic ranges". Regards Vlado "vezerid" wrote: Vlado, The following is not the most elegant solution I can think of, but it works, provided that there are still blank cells between the blocks, after your summation formula is inserted. The following *array* formula is placed directly below a contiguous group of cells in column A, with data starting from A1: =SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(MAX(IF(A $1:A12="",ROW(A$1:A12)))+1,COLUMN()))) As it is an array formula, commit with Shift+Ctrl+Enter. HTH Kostis Vezerides On Oct 11, 2:03 pm, Vlado Sveda wrote: Let imagine this situation: I have column with set of ranges and I need to sumarise every particular range. for example: D4: 9 D5: 5 D6: 3 D7: =SUM(D4:D6) D8: D9: 1 D10: 2 D11: 3 D12: =SUM(D9:D11) D13: D14: 1 D15: 1 D16: 1 D17: 1 D18: 1 D19: 1 D20: 1 D21: =SUM(D14:D20) etc .... My Idea is have in every "Sum cell" the same formula. Every single range can vary in mumber of rows. In VBA I can use .CurrentRegion.Rows.Count. Is something similar possible in sheet formulas ? Thanks in advance to all ! Vlado |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing name range in the same column with Dynamic name ranges | Excel Worksheet Functions | |||
Summing between Range | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Summing a range that changes | Excel Discussion (Misc queries) | |||
dynamic summing of an autofilter 'ed table | Excel Discussion (Misc queries) |