Sum values at each change in level

[gailb]

I would like to sum up some value that basically make up an org chart. Everybody works for level 1 (A2). After that, there are two level 2's, so those some up those who work for them and so on. If the number directly after is not greater than the preceding number, then a zero is fine.

Can this be done via a formula? If not, I can work with VBA.

[HansV]

Could you provide a sample workbook?

[gailb]

Oh sorry, I thought I did.

Sum Totals.xlsx

[HansV]

VBA function:

Code: Select all

Function MySum(AmountRange As Range, LevelRange As Range, Level As Long) As Double
    Dim i As Long
    MySum = AmountRange(1).Value
    For i = 2 To LevelRange.Count
        If LevelRange(i).Value <= Level Then
            Exit For
        End If
        MySum = MySum + AmountRange(i).Value
    Next i
End Function

In C2:

=MySum(B2:B$15,A2:A$15,A2)

Fill down to C15.

[gailb]

Hi Hans and thank you,

In order to hide the results where there isn't a rollup, I used =IF(MySum(B2:B$15,A2:A$15,A2)=B2,0,MySum(B2:B$15,A2:A$15,A2))

This works, but could this also be incorporated into the VBA function?

[HansV]

Yes, like this (although it doesn't make sense to me):

Code: Select all

Function MySum(AmountRange As Range, LevelRange As Range, Level As Long) As Double
    Dim i As Long
    For i = 2 To LevelRange.Count
        If LevelRange(i).Value <= Level Then
            Exit For
        End If
        MySum = MySum + AmountRange(i).Value
    Next i
    If MySum > 0 Then
        MySum = MySum + AmountRange(1).Value
    End If
End Function

[gailb]

Thanks, this will work great.