Re: Sumproduct and wildcards

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
> >
> >
> >


.