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=""),--(MONTH(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(ISNUMBER(SEARCH({"*TECO*";"*CLSD*"},Sheet1!$E$2:$ E$30000))))


Regards,
KL