SumProduct to hard for me :(

freakyOne

New Member
Joined
Feb 13, 2018
Messages
2
i tried this

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
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
I think I'd be inclined to do this as a two-step process:

Code:
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

New Member
Joined
Feb 13, 2018
Messages
2
thank you, i use german language, so i need to translate it but it works, thank you :D
 

Forum statistics

Threads
1,085,436
Messages
5,383,657
Members
401,845
Latest member
ZmutLarr

Some videos you may like

This Week's Hot Topics

Top