Re: Sumproduct and wildcards

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" <vipa2000@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:0180D597-5639-43B6-9EEE-B17FB2774AC7@xxxxxxxxxxxxxxxx
> > 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" <vipa2000@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:EC737C4E-2D17-47A6-944E-5731CFFD135D@xxxxxxxxxxxxxxxx
> > > > 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
> > >
> > >
> > >
>
>
>
.

Loading