SumProduct in an User Defined Function

[Guest]

Hello,
I'm having trouble again... using SumProduct in my UDF.

Example worksheet:
A B C D
1 5/1/07 L.A. Toyota 3
2 5/1/07 N.Y. Honda 2
3 5/2/07 L.A. Toyota 4
4 5/2/07 L.A. Honda 2

If I did in a cell =SUMPRODUCT( (B1:B4 = "L.A.") * (C1:C4 = "Toyota") * D14)
I would get 7, for all Toyotas sold in L.A.

I have tried to do this in a user defined function (because what I'm doing
is FAR more complex, and I don't want to type in EXTREEMLY long formulas in
TONS of cells).

I tried:

myFunction = WorksheetFunction.Sumproduct(
(Worksheet("Sheet1").Range("B1:B4") = "L.A.") *
(Worksheet("Sheet1").Range("C1:C4") = "Toyota") *
Worksheet("Sheet1").Range("D14") )

And I get a value error.

I also tried doing:

Dim range1 As Range
Dim range2 As Range
Dim range3 As Range
Set range1 = Worksheet("Sheet1").Range("B1:B4")
Set range2 = Worksheet("Sheet1").Range("C1:C4")
Set range3 = Worksheet("Sheet1").Range("D14")
myFunction = WorksheetFunction.Sumproduct( (range1 = "L.A.") * (range2 =
"Toyota") * range3)

Same problem.

I also tried
myFunction = WorksheetFunction.Sumproduct( range1 = "L.A.", range2 =
"Toyota", range3)

No luck.

Any ideas anyone?

 

[RB Smissaert]

You could do something like this:

Function SumIf(rng1 As Range, _
rng2 As Range, _
rng3 As Range, _
strLookup1 As String, _
strLookup2 As String) As Double

Dim i As Long
Dim arr1
Dim arr2
Dim arr3

arr1 = rng1
arr2 = rng2
arr3 = rng3

For i = 1 To UBound(arr1)
If arr1(i, 1) = strLookup1 Then
If arr2(i, 1) = strLookup2 Then
SumIf = SumIf + arr3(i, 1)
End If
End If
Next i

End Function


Sub test()

MsgBox SumIf(Range("B1:B4"), Range("C1:C4"), Range("D14"), "L.A.",
"Toyota")

End Sub


But it looks you might be better of using a database and run SQL to get your
data.

RBS

 

[Don Guillett]

This should help
Sub dosumproduct()
x = Evaluate("SUMPRODUCT((B8:B11=""L.A."")*(C8:C11=""Toyota"")*D811)")
MsgBox x
End Sub

 

[Guest]

Wow - that really helped. I'm almost done - but now I need to get rng1,
rng2, and rng3 "hard coded" instead of being parameters.

I tried this, but had no luck... (yes, the worksheet name is "1" not
"Sheet1")

Function Test3(strLookup1 As String, strLookup2 As String) As Double

Dim i As Long
Dim arr1
Dim arr2
Dim arr3

Set arr1 = Worksheets("1").Range("A3:A5")
Set arr2 = Worksheets("1").Range("B3:B5")
Set arr3 = Worksheets("1").Range("C3:C5")

For i = 1 To UBound(arr1)
If arr1(i, 1) = strLookup1 Then
If arr2(i, 1) = strLookup2 Then
Test3 = Test3 + arr3(i, 1)
End If
End If
Next i

End Function


Any suggestions?

 

[RB Smissaert]

In that case do it like this:

Function SumIf(strLookup1 As String, _
strLookup2 As String) As Double

Dim i As Long
Dim arr1
Dim arr2
Dim arr3

With Worksheets("1")
arr1 = .Range("A3:A5")
arr2 = .Range("B3:B5")
arr3 = .Range("C3:C5")
End With

For i = 1 To UBound(arr1)
If arr1(i, 1) = strLookup1 Then
If arr2(i, 1) = strLookup2 Then
SumIf = SumIf + arr3(i, 1)
End If
End If
Next i

End Function


RBS