I think i must have reached saturation last night. Right my code is as
below. Still getting the value error problem. Column E is formatted as text.

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


"Bob Phillips" wrote:

Hi Vipa,

Yeah I sleep. Just different time zones.

The TECO, CLSD values can't be in column I as we have already ascertained
that column I contains dates. I think you must mean another column.

--

HTH

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


"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