Sum in hierarchy

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hideki - 22 Feb 2006 15:58 GMT

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

Signature

hideki

Reply to this Message

Conan Kelly - 22 Feb 2006 18:04 GMT

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

> Hi, I'm stuck on how to calculate sum within hierarchy. Below is
> what
[quoted text clipped - 31 lines]
> Please help,
> hideki

Reply to this Message

Chris Marlow - 22 Feb 2006 18:08 GMT

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.

Signature

Chris Marlow
MCSD.NET, Microsoft Office XP Master

> 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
[quoted text clipped - 27 lines]
> Please help,
> hideki

Reply to this Message

hideki - 23 Feb 2006 18:24 GMT

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.

Signature

hideki

Reply to this Message