I'm not sure if this is more efficient, but here's another way...

=SUMPRODUCT(ISNUMBER(MATCH(Sheet1!$C$2:$C$30000,{1 ,2},0))*(Sheet1!$D$2:$D
$30000="")*(Sheet1!$I$2:$I$30000-DAY(Sheet1!$I$2:$I$30000)+1=DATE(H2,F2,1
))*ISNUMBER(SEARCH({"TECO","CLSD"},Sheet1!$E$2:$E$ 30000)))

Hope this helps!

In article ,
"vipa2000" wrote:

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. Also I
now 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?

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

--
Regards vipa


"KL" wrote:

Hmmm... This is nice - having written a large posting explaining that
SUMPRODUCT can't handle arrays that are of different dimensions, I finally
suggest a formula that does exactly that :-O

Here goes my third try (sorry for being so hasty) :

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

(please also note that in my previous formula I mistakenly put semi-colon
separator instead of comma in {"*TECO*","*CLSD*"})

Now, this formula will work fine as long as there are no strings in column
[E] that include both TECO and CLSD, e.g. "123TECOCLSD@", in which case it
will double count rows.

Regards,
KL


"KL" wrote in message
...
Correction. The following passage:

any of the values "*TECO*" & "*CLSD*" is present in the column it will
return 59,998 otherwise it will return 0

should read as follows:

------------------
one of the values "*TECO*" & "*CLSD*" is present in the column it will
return the number of lines where the rest of conditions together are met

both values "*TECO*" & "*CLSD*" are present in the column it will
return twice the number of lines where the rest of conditions together
are
met

none of the values "*TECO*" & "*CLSD*" is present in the column it will
return 0
------------------

Also please note that the final part of your
formula --(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"})) was making
reference to the sheet where the formula is and not to the Sheet1 as the
rest of the arguments. This may well be on purpose, but if it wasn't, my
final suggested formula should look like this:

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONT
H(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(ISNUMBER(
SEARCH({"*TECO*";"*CLSD*"},Sheet1!$E$2:$E$30000))) )


Regards,
KL