Re: Sumproduct and wildcards

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