VBA: adding up irregular ranges

Question

I need some help to create a macro which adds all the values on the column E between the rows with the "avg" word. the result should be displayed on the cells where the "Sum here" label is displayed. Both texts "avg" and "sum here" is just for illustrate the example, "avg" could be replaced by any other word and "sum here" should actually be the aggregation of the values above it.

The real challenge is that the number of ranges on column E is variable, so i would like to find a macro which is able to deal with "n" number of ranges on column E.

Finally, the values on column D are only the example of the expected value on the "sum here" cells.

This is what I have tried to far:

        Sub Macro1()
'
' Macro1 Macro
'
Dim sumhere As Range
Dim startingpoint As Range
Dim endingpoint As Range

'
    Range("C17").Select
    Selection.End(xlDown).Select
    If ActiveCell = "avg" Then
        ActiveCell.Offset(rowoffset:=0, columnoffset:=2).Select
        
        
        Set sumhere = ActiveCell
        Set startingpoint = ActiveCell.Offset(rowoffset:=-1, columnoffset:=0)
        
        
        Selection.End(xlUp).Select
        If (ActiveCell.Value) = "Sum here" Then
        Set endingpoint = ActiveCell.Offset(rowoffset:=1, columnoffset:=0)
        sumhere.Formula = "=sum(range(startingpoint:endingpoint)"
        
        
            
        Else
        Selection.End(xlUp).Select
        If (ActiveCell.Value) = "Sum here" Then
        Set endingpoint = ActiveCell.Offset(rowoffset:=1, columnoffset:=0)
        sumhere.Formula = "=Sum(Range(startingpoint.adress:endingpoint.adress))"
        
     
        Else: End If
        End If
    
    End If

End Sub

Additionally, as you can see, I do not know, how to define a range using variables. My original idea was to combine this code with some kind of "do while" or/and "for i= 1 to x" and "next i". But I can't see how to combine it.

Answer 1

Using formula only, and providing that column A only has avg (or any text) on each subtotal row.

I've given two versions of the formula - the volatile version (updates everytime you change anything on the spreadsheet), and the non-volatile version (only updates if it needs to).

The formula should be entered on row 6 - change the $E6 to which ever row you need.

(volatile)

=SUM(OFFSET($E6,IFERROR(LOOKUP(2,1/($A$1:INDEX($A:$A,ROW()-1)<>""),ROW($A$1:INDEX($A:$A,ROW()-1))),0)-ROW()+1,,ROW()-1-IFERROR(LOOKUP(2,1/($A$1:INDEX($A:$A,ROW()-1)<>""),ROW($A$1:INDEX($A:$A,ROW()-1))),0)))

(non volatile):

=SUM(INDEX($E:$E,IFERROR(LOOKUP(2,1/($A$1:INDEX($A:$A,ROW()-1)<>""),ROW($A$1:INDEX($A:$A,ROW()-1))),0)+1):INDEX($E:$E,ROW()-1)) 

---

or if you don't mind using a helper column:

In cell B6:

=IFERROR(LOOKUP(2,1/($A$1:INDEX($A:$A,ROW()-1)<>""),ROW($A$1:INDEX($A:$A,ROW()-1))),0)

In E6: (volatile)

=SUM(OFFSET($E6,$B6-ROW()+1,,ROW()-1-$B6))

or (non volatile):

=SUM(INDEX($E:$E,$B6):INDEX($E:$E,ROW()-1))

Edit: Thought I'd add a UDF to calculate it to if you're after VBA.

Use the function =AddSubTotal() in the rows you want the sub total to be shown in, or use =AddSubTotal("pop",6) to sum everything in column F (col 6) using "pop" rather than "avg".

Public Function AddSubTotal(Optional Delim As String = "avg", Optional ColNumber = 5) As Double

    Dim rCaller As Range
    Dim rPrevious As Range
    Dim rSumRange As Range

    Set rCaller = Application.Caller

    With rCaller.Parent
        Set rPrevious = .Range(.Cells(1, 1), .Cells(rCaller.Row - 1, 1)).Find(Delim, , , , , xlPrevious)
        If Not rPrevious Is Nothing Then
            Set rSumRange = rPrevious.Offset(1, ColNumber - 1).Resize(rCaller.Row - rPrevious.Row - 1)
        Else
            Set rSumRange = .Range(.Cells(1, ColNumber), .Cells(rCaller.Row - 1, ColNumber))
        End If
    End With

    AddSubTotal = WorksheetFunction.Sum(rSumRange)

End Function