Sum offset from found range

[TheOneWeDreamOf]

Hi!

I want to sum the values in column E by a found value in column A
The answer of the sum must be displayed in a cell on another worksheet "NewData"

So for example:

Because the search word is "hello" the sum is E2 + E3 + E4 + E8

sumhello.png

This sum should be displayed in a cell on the other sheet

I got to this but it doesn't work. Don; t know how to sum the offsetted values. Perhaps I took a wrong turn with this

Sub SumFoundOffset()

Dim wsData As Worksheet
Set wsData = Worksheets("Data")

Dim wsNewData As Worksheet
Set wsNewData = Worksheets("NewData")

Dim dataLastRow As Long
Dim i As Long
dataLastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To dataLastRow
        If wsData.Cells(i, 1).Value = "Houten kozijn" Then
            
           wsNewData.Cells("A1") = WorksheetFunction.Sum wsData.Cells(i, 5).Value
        
        End If
Next

End Sub

Hope this is clear enough. There is a sample file attached. Any help would be more than welcome!

Thanks!



note: also posted this on a dutch support forum