Count string IF.....
 

I am trying to count a 3 digit embeded data string IF....

NAME DATA_STRING
Joe WAPDATMM4RT1RRR
Susan WAPDATPLBMM5UDF
John DATPLBINSRBTMM6
Joe WAPUDARBTRRRMM4

I can count the strings with...
=SUM(COUNTIF(B2:B5,"*WAP*"),COUNTIF(B2:B5,"*RBT*") ,COUNTIF(B2:B5,"*MM4*"))
result (7)
I need the result IF A2:A5=Joe result (5)
I have stared at SUMPRODUCT long enough....H E L P !

Count string IF.....
 

One way:
=SUMPRODUCT((ISNUMBER(FIND({"WAP","RBT","MM4"},B2: B5)))*(A2:A5="Joe"))

Replace FIND with SEARCH if you don't need it to be case sensitive
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Coley" wrote:
I am trying to count a 3 digit embeded data string IF....

NAME DATA_STRING
Joe WAPDATMM4RT1RRR
Susan WAPDATPLBMM5UDF
John DATPLBINSRBTMM6
Joe WAPUDARBTRRRMM4

I can count the strings with...
=SUM(COUNTIF(B2:B5,"*WAP*"),COUNTIF(B2:B5,"*RBT*") ,COUNTIF(B2:B5,"*MM4*"))
result (7)
I need the result IF A2:A5=Joe result (5)
I have stared at SUMPRODUCT long enough....H E L P !

Count string IF.....
 

OUTSTANDING! Not sure why it works but it does. Thanks!

"Max" wrote:

One way:
=SUMPRODUCT((ISNUMBER(FIND({"WAP","RBT","MM4"},B2: B5)))*(A2:A5="Joe"))

Replace FIND with SEARCH if you don't need it to be case sensitive
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Coley" wrote:
I am trying to count a 3 digit embeded data string IF....

NAME DATA_STRING
Joe WAPDATMM4RT1RRR
Susan WAPDATPLBMM5UDF
John DATPLBINSRBTMM6
Joe WAPUDARBTRRRMM4

I can count the strings with...
=SUM(COUNTIF(B2:B5,"*WAP*"),COUNTIF(B2:B5,"*RBT*") ,COUNTIF(B2:B5,"*MM4*"))
result (7)
I need the result IF A2:A5=Joe result (5)
I have stared at SUMPRODUCT long enough....H E L P !

Count string IF.....
 

welcome, Coley. glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Coley" wrote in message
...
OUTSTANDING! Not sure why it works but it does. Thanks!

Count string IF.....
 

"Max" wrote...
One way:
=SUMPRODUCT((ISNUMBER(FIND({"WAP","RBT","MM4"},B2 :B5)))*(A2:A5="Joe"))
....

Another way, shorter but an array formula,

=COUNT(FIND({"WAP","RBT","MM4"},B2:B5)/(A2:A5="Joe"))