PDA

View Full Version : A Simple Pyramid Totals



etmzkn
03-24-2016, 01:41 AM
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
03-25-2016, 06:11 AM
Any ideas?

mancubus
03-25-2016, 07:36 AM
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
03-25-2016, 07:50 AM
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
03-25-2016, 08:27 AM
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
03-25-2016, 04:48 PM
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
03-25-2016, 04:50 PM
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.