i tried this
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
Code:
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