Andi,

I would try breaking the problem down to find where the error arises.

Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")*1)")
Evaluate("SUMPRODUCT((" & strTask_Finish & "=" & str_Date & ")*1)")
Evaluate("SUMPRODUCT((" & strTask_Type & "=" & ColType & ")*1)")
Evaluate("SUMPRODUCT((" & strTask_DCIT & ")*1)")

HTH,
Bernie
MS Excel MVP


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

I tried your suggestion but still get the same result. I now have the
following formula:-

Evaluate("SUMPRODUCT((" & strTask_Start & "<=" & str_Date & ")" & _
"*(" & strTask_Finish & "=" & str_Date & ")*" & _
"(" & strTask_Type & "=" & ColType & ")*(" & strTask_DCIT & "))")

Which gives the result #Value!

The formula is evaluating the following:-
=SUMPRODUCT(('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Start<='Release
Schedule (2)'!$A$168)*('Working - Dependency Chart
V50_V0.4.xls'!LU_DC_Finish='Release Schedule (2)'!$A$168)*('Working -
Dependency Chart V50_V0.4.xls'!LU_DC_Type="SMV - Pre-Prod -
Maintance")*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_ID))

Which equates to 10

If I try

MsgBox Evaluate("SUMPRODUCT(('Working - Dependency Chart
V50_V0.4.xls'!LU_DC_Start<='Release Schedule (2)'!$A$168)*('Working -
Dependency Chart V50_V0.4.xls'!LU_DC_Finish='Release Schedule
(2)'!$A$168)*('Working - Dependency Chart V50_V0.4.xls'!LU_DC_Type=""SMV -
Pre-Prod - Maintance"")*('Working - Dependency Chart
V50_V0.4.xls'!LU_DC_ID))")

I get a run-tim error type mismatch

Not sure if this helps.

Andi



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Then I would suspect that the 'switching' of sides is the problem:

In the first, ColType is on the right of the = sign:
(" & strTask_Type & " = " & ColType & ")

In the third, ColType is on the left:

(""" & ColType & """=" & strTask_Type & ")

Generally: multicells = onecell, or multicell = constant
HTH,
Bernie
MS Excel MVP


"Andibevan" wrote in message
...
Bernie - It should be in quotes - It is in quotes on my version, I must
have
messed it up while tranfering it to an e-mail.



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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