A Simple Pyramid Totals

[etmzkn]

Hi everyone.

Is there a easy way to sum different rows in a worksheet to create a subtotal? I just want to use sum formula (sum(A1,A5,A9) etc.) not sumifs or sumproduct. I know it should be really easy but I couldnt do it.


Best.

[etmzkn]

Any ideas?

[mancubus]

welcome to vbax etmzkn

your workbook tells me nothing about your requirement.

so i take into account the first message and assume you want to sum non-contiguous cells (i assume you mean cells by rows)

if this is what you are after, it's too simple, try:

1st method
select the cell yo want the sum values.
press AutoSum. Excel will insert a SUM() function for the closest possible range.
clear the range in formula
select the first cell to sum, hold down the Ctrl key and select second cell.
select as many cells as you wish to add while holding down Ctrl key.
press Enter

you will see a formula like

=SUM(F13,F20,H22,H30) 

2nd method
select the cell
press = key
then select the first cell, press + key, select second cell
select as many cells as you wish to add by inputting + between cells
press Enter
you will see a formula like

=F13+F20+H22+H30 

if this is not what you are after, try to explain clearly what you need...

[etmzkn]

First of all thank you for your reply.

I have updated sample worksheet with descriptions. Also you can see my try on VBA view.

Hope I could explain myself.

Best.

[mancubus]

yes, thats it. now you now how to demonstrate your questions / requirements here.

i'm quite busy at the moment and will work on it later.

other members may post a solution too.

[mancubus]

i assume your table is sorted ascending based on column B.

the order is
1
11
111
2
22
222
2222

and not
1
12
2121
11
211
2
1111

and i assume maximum heading level is 4
1
11
111
1111

not
1
11
111
1111
11111

[mancubus]

Sub vbax_55537_multi_level_subtotals()

    Dim i As Long, j As Long, LevelSum As Double

    'Summing Level4 numbers for Level3 values
    For i = Cells(Rows.Count, 2).End(xlUp).Row To 3 Step -1
        If Len(Cells(i, 2)) = 4 Then
            LevelSum = 0
            LevelSum = LevelSum + Cells(i, 3)
            For j = i - 1 To 3 Step -1
                If Len(Cells(j, 2)) = 3 Then
                    Cells(j, 3) = LevelSum
                    i = j
                    Exit For
                ElseIf Len(Cells(j, 2)) = 4 Then
                    LevelSum = LevelSum + Cells(j, 3)
                End If
            Next j
        End If
    Next i
    
    'Summing Level3 numbers for Level2 values
    For i = Cells(Rows.Count, 2).End(xlUp).Row To 3 Step -1
        If Len(Cells(i, 2)) = 3 Then
            LevelSum = 0
            LevelSum = LevelSum + Cells(i, 3)
            For j = i - 1 To 3 Step -1
                If Len(Cells(j, 2)) = 2 Then
                    Cells(j, 3) = LevelSum
                    i = j
                    Exit For
                ElseIf Len(Cells(j, 2)) = 3 Then
                    LevelSum = LevelSum + Cells(j, 3)
                End If
            Next j
        End If
    Next i
    
    'Summing Level2 numbers for Level1 values
    For i = Cells(Rows.Count, 2).End(xlUp).Row To 3 Step -1
        If Len(Cells(i, 2)) = 2 Then
            LevelSum = 0
            LevelSum = LevelSum + Cells(i, 3)
            For j = i - 1 To 3 Step -1
                If Len(Cells(j, 2)) = 1 Then
                    Cells(j, 3) = LevelSum
                    i = j
                    Exit For
                ElseIf Len(Cells(j, 2)) = 2 Then
                    LevelSum = LevelSum + Cells(j, 3)
                End If
            Next j
        End If
    Next i

End Sub

PS: before running the code, make sure the cells into which the sums will be inserted are cleared.