SumProduct to hard for me :(

[freakyOne]

i tried this

With Worksheets("Hilfstabelle")
    Worksheets("Linienauswertung").Range("G14").Value = Application.WorksheetFunction.SumProduct((.Range("D2:D" & Zeilenzahl)) * (.Range("A2:A" & Zeilenzahl) Mod 10), "<9")
    End With

what i want is, sum the values in Column D for each row, where the value in column A does NOT have a 9 at 4th sign (it has only 4 numbers, like 1119, 1190, 3543, 7769...)

i tried it with modulo and sumproduct but failed

First try was this

Worksheets("Linienauswertung").Range("B" & i + 14).Value = Application.SumIfs(.Range("D2:D" & Zeilenzahl), .Range("C2:C" & Zeilenzahl), "FS", Right(.Range("A2:A" & Zeilenzahl), 1), "<>9")
is there an easier way to tell him skip if there is a 9 at the end of the Value?

Greetings and thanks

 

[wideboydixon]

I think I'd be inclined to do this as a two-step process:

With Worksheets("Linienauswertung").Range("G14")
    .FormulaArray = "=SUM(IF(MOD(Hilfstabelle!A2:A" & Zeilenzahl & ",10)<9,Hilfstabelle!D2:D" & Zeilenzahl & "))"
    .Value = .Value
End With

WBD

 

[freakyOne]

thank you, i use german language, so i need to translate it but it works, thank you :D