PDA

View Full Version : Solved: SUMPRODUCT across 4 columns



lifeson
10-14-2008, 10:03 AM
CAn someone hlep me with a sumproduct across 4 columns

I have got to the following but my head hurts:(

Where:
id is a string variable
chkDate is Long

With ws
hrsUsedInYr = WorksheetFunction.SumProduct(--("A2:A100" = id), --("B2:B100" = "H"), --("C2:C100" >=chkDate),"E2:E100")
end with


Thanks

mdmackillop
10-14-2008, 10:31 AM
You can't use SumProduct with WorksheetFunction.. Use Evaluate


Dim tmp As String
tmp = "=SumProduct(--(A2:A100 = " & ID & "), --(B2:B100 = " & """H""" & "), --(C2:C100 >= " & chkdate & "), E2:E100)"
Debug.Print tmp
With ActiveSheet
hrsUsedInYr = Evaluate(tmp)
End With

lifeson
10-15-2008, 07:17 AM
Thanks MDM
Not sure how that works but it has
Evaluate is a new one for me.