Re: Sumproduct and wildcards

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