Hi vipa,


"vipa2000" wrote in message
...
thanks KL, worked a treat. Do i need to be worried about the loss of
the --
prefixes? I have read a number of articles on the importance of these.

See JE McGimpsey's post - pretty much what I would say too.

... want the code to look for type 2's and not just type 1's (first bit of
code.) If there an efficient way to do this rather than reapeating the
code
and adding the two together?

Not sure about efficiency - this type of formulae are highly "expensive"
especially when dealing with 30,000-row range and/or replicated in various
cells, but you could try this one:

=SUMPRODUCT(((Sheet1!$C$2:$C$30000=1)+(Sheet1!$C$2 :$C$30000=2)0)*(Sheet1!$D$2:$D$30000="")*(MONTH(S heet1!$I$2:$I$30000)=F2)*(YEAR(Sheet1!$I$2:$I$3000 0)=H2)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1! $E$2:$E$30000)))

Regards,
KL