From the immediate window:

? Evaluate(
"SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rng2)=""B"" )*(rng3=""""))")
2

With defined names of rng, rng2, rng3, it worked fine for me. Ir rng, rng2
and rng3 are vba object references, then that will not work. In that case,
to fix:

rng.name = "rng"
rng1.name = "rng1"
rng2.name = "rng2"
' then use your formula


--
Regards,
Tom Ogilvy

Gareth wrote in message
...
I find myself in the position of having to use code instead of functions.

My original function in E7 was as follows:

=SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rng 3=""))

I have put the following into the Worksheet Activate event of the new file
but it doesn't appear to work:

Range("E7").Value =

Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rn g2)=""B"")*(rng3=""""))")

Thanks in advance.

Gareth