Sum in hierarchy

Hi, I'm stuck on how to calculate sum within hierarchy. Below is what I'd like to archive. How can I create a macro that in the end will WRITE A FORMULA into the cells for every level? For example, at Level 0, the formula is sum all Level1, at level1 = sum all level2 and so on. The levels are not limited to 4 but will change dynamically.

I'd tried to looking for a start and end row for each level and use it in a sum formula at the upper level. But I got confuse myself. Nothing's working. Any ideas are very appreciated.

1. L0 = SUM (ALL L1)
2. --L1 = SUM(ALL L2)
3. ---L2 = SUM (ALL L31)
4. ----L31 = SUM (ALL L41)
5. -----L41 10
6. -----L41 26
7. -----L41 33
8. ----L31 = SUM (ALL L42)
9. -----L42 77
10.-----L42 25
11.-----L42 15
12.---L2 = SUM (ALL L32)
13.----L32 = SUM (ALL L43)
14.-----L43 11
15.-----L43 55
16.----L32 = SUM (ALL L44)
17.-----L44 4
18.-----L44 78

Please help,
hideki

Hideki,

Have you tried Subtotals (Data > Subtotals...) and Grouping? It sound
like that will accomplish what you are trying to do.

Hope This Helps,

Conan Kelly



"hideki" <hideki.23n1i0_1140624006.7355@excelforum-nospam.com> wrote
in message news:hideki.23n1i0_1140624006.7355@excelforum-nospam.com...
>
> Hi, I'm stuck on how to calculate sum within hierarchy. Below is
> what
> I'd like to archive. How can I create a macro that in the end will
> WRITE A FORMULA into the cells for every level? For example, at
> Level
> 0, the formula is sum all Level1, at level1 = sum all level2 and so
> on.
> The levels are not limited to 4 but will change dynamically.
>
> I'd tried to looking for a start and end row for each level and use
> it
> in a sum formula at the upper level. But I got confuse myself.
> Nothing's working. Any ideas are very appreciated.
>
> 1. L0 = SUM (ALL L1)
> 2. --L1 = SUM(ALL L2)
> 3. ---L2 = SUM (ALL L31)
> 4. ----L31 = SUM (ALL L41)
> 5. -----L41 10
> 6. -----L41 26
> 7. -----L41 33
> 8. ----L31 = SUM (ALL L42)
> 9. -----L42 77
> 10.-----L42 25
> 11.-----L42 15
> 12.---L2 = SUM (ALL L32)
> 13.----L32 = SUM (ALL L43)
> 14.-----L43 11
> 15.-----L43 55
> 16.----L32 = SUM (ALL L44)
> 17.-----L44 4
> 18.-----L44 78
>
> Please help,
> hideki
>
>
> --
> hideki
> ------------------------------------------------------------------------
> hideki's Profile:
> http://www.excelforum.com/member.php...o&userid=18903
> View this thread:
> http://www.excelforum.com/showthread...hreadid=515368
>

Hideki,

This is not an answer unless you can get your base data recut but I'll
suggest it anyway. If your hierarchy information were laid out in columns to
the left of your data, then you could use grouping & outlining.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"hideki" wrote:

>
> Hi, I'm stuck on how to calculate sum within hierarchy. Below is what
> I'd like to archive. How can I create a macro that in the end will
> WRITE A FORMULA into the cells for every level? For example, at Level
> 0, the formula is sum all Level1, at level1 = sum all level2 and so on.
> The levels are not limited to 4 but will change dynamically.
>
> I'd tried to looking for a start and end row for each level and use it
> in a sum formula at the upper level. But I got confuse myself.
> Nothing's working. Any ideas are very appreciated.
>
> 1. L0 = SUM (ALL L1)
> 2. --L1 = SUM(ALL L2)
> 3. ---L2 = SUM (ALL L31)
> 4. ----L31 = SUM (ALL L41)
> 5. -----L41 10
> 6. -----L41 26
> 7. -----L41 33
> 8. ----L31 = SUM (ALL L42)
> 9. -----L42 77
> 10.-----L42 25
> 11.-----L42 15
> 12.---L2 = SUM (ALL L32)
> 13.----L32 = SUM (ALL L43)
> 14.-----L43 11
> 15.-----L43 55
> 16.----L32 = SUM (ALL L44)
> 17.-----L44 4
> 18.-----L44 78
>
> Please help,
> hideki
>
>
> --
> hideki
> ------------------------------------------------------------------------
> hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
> View this thread: http://www.excelforum.com/showthread...hreadid=515368
>
>

I'd came close with below loops. But it gave me #VALUE or #NAME error. Here is what I did, I go to the Lowlevel (eg 4) and record it row's number - the column is still unchange. It's loops until found a level that less one than current level. Then the record will restart.

Problem here is, I record column and row's number as string. It's seems to cause the error. It became like A119, A118, A117 etc. In the final I'd like to make it became "=SUM(A119,A118,A117)" but the actual formula wrote in the cell was =SUM('A119','A118','A117') and it's not summing anything. Any idea how can I make the formula working?

Here is my code:

strSum = ""
For lngLevel = LevelMax To 1 Step -1
For lngRow = lngLastRow To 3 Step -1
Select Case .Cells(lngRow, colLevel).Value
Case lngLevel
strSum = strSum & strColTotal & lngRow & ","
Case lngLevel - 1
strSum = Mid(strSum, 1, Len(strSum) - 1) & ")"
.Cells(lngRow, colTotal).FormulaR1C1 = "=SUM(" & strSum & ")"
strSum = ""
End Select
Debug.Print strSum
Next
Next

Any ideas are really appreciated.