Re: Sumproduct and wildcards

Hmmm... This is nice - having written a large posting explaining that
SUMPRODUCT can't handle arrays that are of different dimensions, I finally
suggest a formula that does exactly that :-O

Here goes my third try (sorry for being so hasty) :

=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)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1!$E$2:$E$30000)))

(please also note that in my previous formula I mistakenly put semi-colon
separator instead of comma in {"*TECO*","*CLSD*"})

Now, this formula will work fine as long as there are no strings in column
[E] that include both TECO and CLSD, e.g. "123TECOCLSD@", in which case it
will double count rows.

Regards,
KL


"KL" <NOSPAMlapink2000@xxxxxxxxxxxxxxxxx> wrote in message
news:uvaEDwblFHA.1048@xxxxxxxxxxxxxxxxxxxxxxx
> Correction. The following passage:
>
>> any of the values "*TECO*" & "*CLSD*" is present in the column it will
>> return 59,998 otherwise it will return 0
>
> should read as follows:
>
> ------------------
> one of the values "*TECO*" & "*CLSD*" is present in the column it will
> return the number of lines where the rest of conditions together are met
>
> both values "*TECO*" & "*CLSD*" are present in the column it will
> return twice the number of lines where the rest of conditions together are
> met
>
> none of the values "*TECO*" & "*CLSD*" is present in the column it will
> return 0
> ------------------
>
> Also please note that the final part of your
> formula --(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"})) was making
> reference to the sheet where the formula is and not to the Sheet1 as the
> rest of the arguments. This may well be on purpose, but if it wasn't, my
> final suggested formula should look like 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),--(ISNUMBER(SEARCH({"*TECO*";"*CLSD*"},Sheet1!$E$2:$E$30000))))
>
>
> Regards,
> KL
>


.

Quantcast