Hi vipa,

The following parts of your formula:
--(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)
return a 29999-member vertical array each

while the last part:

--(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"}))

returns a 2-member horizontal array

I guess, problem is that SUMPRODUCT can't handle it on its own and will
return error.

Now, if you substitute the five arguments of SUMPRODUCT by one, where each
part is a multiplier:

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

then SUMPRODUCT will be able to digest it..., but I don't think the formula
would return the correct result. That's because the last part of it
evaluates the whole column and not each reacord in it separately. I.e. if
any of the values "*TECO*" & "*CLSD*" is present in the column it will
return 59,998 otherwise it will return 0

If I understand correctly what you are after, try this formula:

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

I guess it doesn't need to be array-entered, but haven't tested it.

If you want the test SEARCH({"*TECO*";"*CLSD*"},$E$2:$E$30) to be
case-sensitive, then replace SEARCH by FIND.

Hope this helps.

Regards,
KL



"vipa2000" wrote in message
...
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