Andi,

In the first formula, you use

" = " & ColType & ")

In your third formula, you use:

"(""" & ColType & """=

So, should ColType be in quotes or not?

HTH,
Bernie
MS Excel MVP


"Andibevan" wrote in message
...
Hi All,

I am having problems constructing a complex evaluate formula that I am using
within a user defined function:-


This formula works fine:-
Evaluate("SUMPRODUCT((" & strTask_Type & " = " & ColType & ")*(" &
strTask_DCIT & "))")

This also works:-
EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" &
str_Date & "<=" & strTask_Finish & "))")

But when I try to combine the 2 I get a Value# error:-
EvalSTR = ("SUMPRODUCT((" & str_Date & "=" & strTask_Start & ")" & "*(" &
str_Date & "<=" & strTask_Finish & ")*" & "(""" & ColType & """=" &
strTask_Type & ")*(" & strTask_DCIT & "))")

I can't see what's wrong....

Any ideas

Ta

Andi