Count string IF.....

C

Coley

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 !
 
M

Max

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
 
H

Harlan Grove

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

Another way, shorter but an array formula,

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