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("D1:D4"), "L.A.",
"Toyota")

End Sub


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

RBS


"I need help please" wrote in
message ...
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") *
D1:D4)
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("D1:D4") )

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("D1:D4")
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?