Sumproduct Problem

[Andibevan]

Hi All,



I am trying to convert a sumproduct formulae to a UDF - mainly to allow
users to change the addresses with no chance of them messing up the
formulae.



=SUMPRODUCT((IG$2>=Hol_Start)*(IG$2<=Hol_End)*(Hol_Name=$A20)*(Hol_Type_Code
))



Hol_Start & Hol End are named ranges that are dates

Hol_Name is a named range that is a text string

Hol_Type_Code is an integer (all named ranges are same size)



Here is my attempt at the code:-



Function HolAvail(Var_Name, Optional Var_Date)



HolAvail =
Evaluate("SUMPRODUCT((Var_Date>=Hol_Start)*(Var_Date<=Hol_End)*(Hol_Name=Var
_Name)*(Hol_Type_Code))")



End Function



I Can't get it to work though - Keep getting #Value errors



Thanks in advance



Andy

 

[Tom Ogilvy]

Function HolAvail(Var_Name as Range, Optional Var_Date as Variant)
Dim s as String, s1 as String
if ismissing(Var_Date) then
s1 = "IG$2"
Else
s1 = Var_Date.Address
End if
s = Var_Name.Address
HolAvail =
Evaluate("SUMPRODUCT((" & s1 & _
">=Hol_Start)*(" & s1 & "<=Hol_End)*(" & _
"Hol_Name=" & s & ")*(Hol_Type_Code))")

End Function