Adding numbers from an array into a single cell

Hello,
Sorry for not being clear. I have updated the excel. In it there are tables and I need to get the sum of the values of the 'Benefits' row into a cell. So in this case 'G8+G17+G26+G35' goes into one particular cell and similarly other columns (H to Z). As these tables are populated by another macro, the way I'm currently doing is storing G8,G17,G26,G35 in an array but I'm struggling to get 'G8+G17+G26+G35' into one cell that would fit into another table.


Please ignore the poisition of this cell, which has the 'G8+G17+G26+G35' value. I need help with the summation of numbers stored in the array

For the data provided.

Sub test()
    Dim r As Range
    With Sheets("sheet1")
        For Each r In .Columns("e").SpecialCells(2, 1).Areas
            With r.CurrentRegion
                .Offset(2, 3).Resize(, .Columns.Count - 3).Rows(.Rows.Count).FormulaR1C1 = "=product(r" & r.Row & "c:r[-2]c)"
            End With
        Next
    End With
End Sub

Try

Sub test()
    Dim myAreas As Areas, i As Long, ii As Long, a()
    With Sheets("sheet1")
        Set myAreas = .Columns("g").SpecialCells(-4123, 1).Areas
        ReDim a(1 To 1)
        For i = 2 To myAreas.Count Step 2
            If myAreas(i).Rows.Count = 1 Then
                If UBound(a) < myAreas(i).CurrentRegion.Columns.Count Then
                    ReDim a(1 To myAreas(i).CurrentRegion.Columns.Count)
                End If
                For ii = 1 To myAreas(i).CurrentRegion.Columns.Count
                    a(ii) = a(ii) + myAreas(i)(1, ii).Value
                Next
            End If
        Next
        .[g40].Resize(, UBound(a)).Value = a
    End With
End Sub

OOps, I've read your question wrong...

Where do you want the result?

No problem jindon, anywhere may be G40. So G40 = Sum(G8,G17,G26,G35)

Thanks jindon! The code works like magic :D. If you don't mind could you tell me what it exactly does. I'm unable to get my head around it

Is it fixed like this?

Sub test()
    [f98:y98] = "=sum(f32,f42,f52,f62)"
End Sub

That was great, it worked . Thanks!

However I don't know how many cells I need to add, in this case there are four tables hence '=sum(f32,f42,f52,f62)'. It could vary from one to many tables before the 'Costs of Development and Operation' table. But I have the row numbers, is there any way I can generalise this sum function with some counting variable?

Be careful, you have trailing space in B98 and the code will look for that row.

Sub test()
    Dim x, myAreas As Areas, myArea As Range, txt As String, myCols As Long
    With Sheets("Payback Calculator")
        x = Filter(.[transpose(if((b1:b1000="Start Date")+(b1:b1000="Costs of Development and Operation")+(b1:b1000="Revenue Contribution "),row(1:1000)))], False, 0)
        If UBound(x) < 2 Then
            MsgBox "Missing something": Exit Sub
        End If
        Set myAreas = Intersect(.Rows(x(0) & ":" & x(1)), .Columns("f")).SpecialCells(-4123, 1).Areas
        For Each myArea In myAreas
            If (myArea.Count = 1) * (Not IsDate(myArea(1).Value)) Then
                txt = txt & "," & myArea.Address(0, 0)
                myCols = Application.Max(myCols, myArea.CurrentRegion.Columns.Count)
            End If
        Next
        .Cells(x(2), "f").Resize(, myCols) = "=sum(" & Mid(txt, 2) & ")"
    End With
End Sub

Thanks jindon, the code is working perfectly but could you please tell what it does exactly.

Hello pjwhitfield, I'm very sorry. I didn't realise that you replied to this thread. Thanks for your help.