I am trying to create a function that sums a worksheet column based on
criteria that reside in adjacent columns. I used the following code
which gave me a type mismatch:

Function Purch_calc(look1 As String, look2 As String) As Single

Dim l1_range As Range
Dim l2_range As Range
Dim s_range As Range

With Sheet6
Set l1_range = .Range("d1:d200")
Set l2_range = .Range("b1:b200")
Set s_range = .Range("e1:e200")
End With

Purch_calc = Application.WorksheetFunction.SumProduct((l1_range =
look1) * (l2_range = look2), s_range)

End Function

Can anyone help me get the above to work or provide an alternative
method of summing a column using multiple criteria.


Thanks
Paul

Thanks, this is very helpful

Martin Fishlock wrote:
Hi Paul:

As far as I know you can't use array type formula in vba so you have to test
each element seperately.

Look at the code below:

Option Explicit

Function Purch_calc(look1 As String, look2 As String) As Integer

Const l1_offset As Integer = -1 ' Set l1_range = .Range("d1:d10")
Const l2_offset As Integer = -3 ' Set l2_range = .Range("b1:b10")

Dim s_range As Range
Dim rCell As Range

Application.Volatile ' update on changes

Set s_range = ActiveWorkbook.Worksheets("sheet6").Range("e1:e10" )

For Each rCell In s_range
If (rCell.Offset(0, l1_offset) = look1) And _
(rCell.Offset(0, l2_offset) = look2) Then
Purch_calc = Purch_calc + rCell.Value
End If
Next rCell

End Function

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"halibut" wrote:

I am trying to create a function that sums a worksheet column based on
criteria that reside in adjacent columns. I used the following code
which gave me a type mismatch:

Function Purch_calc(look1 As String, look2 As String) As Single

Dim l1_range As Range
Dim l2_range As Range
Dim s_range As Range

With Sheet6
Set l1_range = .Range("d1:d200")
Set l2_range = .Range("b1:b200")
Set s_range = .Range("e1:e200")
End With

Purch_calc = Application.WorksheetFunction.SumProduct((l1_range =
look1) * (l2_range = look2), s_range)

End Function

Can anyone help me get the above to work or provide an alternative
method of summing a column using multiple criteria.


Thanks
Paul