Sumproduct if two conditions are met but using wildcards?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
 
Madduck said:
=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|||||||||||
Click to expand...
....

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

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

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!
 
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 said:
=SUMPRODUCT(--ISNUMBER(FIND("MIC",Data!$H$1:$H$5000)),
--ISNUMBER(FIND("ley Tob",Data!$G$1:$G$5000)))
Click to expand...
....

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.
 
Back
Top