Sumproduct if two conditions are met but using wildcards?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Madduck - 21 Aug 2007 07:02 GMT

Hi Team,

was wondering if you could help me.... I am using the below formula

=SUMPRODUCT(--(Data!$H$1:$H$5000="||||||||MIC|||||||||||"),--(Data!$G$1:$G$5000="Ashley Tobin"))

the problem is, as you can probably see, the data is not very clean..
sometimes the data looks like this ||||||||MIC||||||||||| other times it
will be |||||||Investments & returns|MIC|||||||||||

using count if I can use wildcards and it works nicely.

=COUNTIF(Data!$H:$H,"*MIC*")

but they don't ( or I can't seem to get them to ) work when using
sumproduct... any suggestions??

thanks
Mark...

Reply to this Message

Harlan Grove - 21 Aug 2007 08:08 GMT

"Madduck" <Madduck@discussions.microsoft.com> wrote...
...

>=SUMPRODUCT(--(Data!$H$1:$H$5000="||||||||MIC|||||||||||"),
>--(Data!$G$1:$G$5000="Ashley Tobin"))
>
>the problem is, as you can probably see, the data is not very clean..
>sometimes the data looks like this ||||||||MIC||||||||||| other times it
>will be |||||||Investments & returns|MIC|||||||||||

...

=SUMPRODUCT(--ISNUMBER(FIND("|MIC|",Data!$H$1:$H$5000)),
--(Data!$G$1:$G$5000="Ashley Tobin")))

Reply to this Message

T. Valko - 21 Aug 2007 22:36 GMT

> "Madduck" <Madduck@discussions.microsoft.com> wrote...
> ...
[quoted text clipped - 8 lines]
> =SUMPRODUCT(--ISNUMBER(FIND("|MIC|",Data!$H$1:$H$5000)),
> --(Data!$G$1:$G$5000="Ashley Tobin")))

Geez! Here I thought those "vertical bars" were some kind of strange
formatting escape chararcter that I've never seen before but they're just
your standard pipe characters!

Signature

Biff
Microsoft Excel MVP

Reply to this Message

Madduck - 21 Aug 2007 23:08 GMT

Thank you very much, that worked a treat... I expanded a little for the
formula to read :

=SUMPRODUCT(--ISNUMBER(FIND("MIC",Data!$H$1:$H$5000)),
--ISNUMBER(FIND("ley Tob",Data!$G$1:$G$5000)))

now it really doesn't matter how clean the data is..

Thanks again Harlan.

Mark.

> "Madduck" <Madduck@discussions.microsoft.com> wrote...
> ....
[quoted text clipped - 8 lines]
> =SUMPRODUCT(--ISNUMBER(FIND("|MIC|",Data!$H$1:$H$5000)),
> --(Data!$G$1:$G$5000="Ashley Tobin")))

Reply to this Message

Harlan Grove - 21 Aug 2007 23:29 GMT

"Madduck" <Madduck@discussions.microsoft.com> wrote...
...

>=SUMPRODUCT(--ISNUMBER(FIND("MIC",Data!$H$1:$H$5000)),
>--ISNUMBER(FIND("ley Tob",Data!$G$1:$G$5000)))

...

I didn't put the vertical bars in my formula by accident. If 'MIC' could
appear as a substring in some other bar-delimited field in col H, then you
could get false hits searching for "MIC" rather then searching for the
bar-delimited field "|MIC|". For example,

|||||||SYSTEMIC FAILUE||||||||

would be treated as a match in your formula.

Reply to this Message

Madduck - 24 Sep 2007 06:20 GMT

Thanks Harlan,

yep I did realise that.

Thanks again.

> "Madduck" <Madduck@discussions.microsoft.com> wrote...
> ....
[quoted text clipped - 10 lines]
>
> would be treated as a match in your formula.

Reply to this Message