Vipa: I'm convinced, too, that Bob never sleeps.
*******************
~Anne Troy

www.OfficeArticles.com


"vipa2000" wrote in message
...
Bob, do you ever sleep? my thanks to you again. My formula is as below


=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),--(COUNTIF($I$2:
$I$30000,{"*TECO*","*CLSD*"}))

it is coming up with a value error. Checking the error it states that a
valur used in the formula is the wrong data type. I think it is just
me!!!!
--
Regards vipa


"Bob Phillips" wrote:

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use
something
that
allows me to do 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))

The above works fine thanks to a lot of help from Bob. However I now
need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa