Sumproduct if two conditions are met but using wildcards?
 

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

Sumproduct if two conditions are met but using wildcards?
 

"Madduck" 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")))

Sumproduct if two conditions are met but using wildcards?
 

"Harlan Grove" wrote in message
...
"Madduck" 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")))

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!

--
Biff
Microsoft Excel MVP

Sumproduct if two conditions are met but using wildcards?
 

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.




"Harlan Grove" wrote:

"Madduck" 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")))

Sumproduct if two conditions are met but using wildcards?
 

"Madduck" 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.

Sumproduct if two conditions are met but using wildcards?
 

Thanks Harlan,

yep I did realise that.

Thanks again.

"Harlan Grove" wrote:

"Madduck" 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.