0

I already use these below formula to count exact text contain in string but still formula wrongly counted it. For example, i would like to count "ZIKA" test code in table, the answer should be two. But the formula count ZIKA2 as ZIKA also. How to ignore ZIKA2 from count it?

TEST

  • HS2, CCAL, EGFR, AFB
  • ZIKA, AG21
  • PPB, ZIKA2
  • ZIKA, AG21

I already try these formulas: 

=SUMPRODUCT(--(ISNUMBER(FIND("ZIKA",F:F))))

and also

=COUNTIF(F:F,"ZIKA")
Kamalisto
  • 13
  • 1
  • 1
  • 5

3 Answers3

0

you could count exact zika, and comma-separated vriations

=COUNTIF(F:F,"ZIKA")+COUNTIF(F:F,"ZIKA,*")+COUNTIF(F:F,"*, ZIKA")+COUNTIF(F:F,"*, ZIKA,*")

AlexandrX
  • 806
  • 8
  • 18
0

Try this regex, it may need a helpercolumn. I have not tested it that much yet.
Press ALT + F11 to open VBA editor.
Click Insert -> module and copy paste the code below.

Function Regex(Cell, Search)
    Dim RE As Object

    Set RE = CreateObject("vbscript.regexp")

    RE.Pattern = "(\b" & Search & "\b)"
    RE.Global = True
    RE.IgnoreCase = True
    Set Matches = RE.Execute(Cell)

    For Each res In Matches
        Regex = Regex & "," & res
    Next res
    Regex = Mid(Regex, 2)
End Function

It will return "ZIKA" if it finds ZIKA in the cell you run it on.
And then you just count the ZIKAs in the helper column.

Updated with a new code that you can change the search in.
Use it with =regex(A1, "ZIKA")

enter image description here

Andreas
  • 23,610
  • 6
  • 30
  • 62
0

I assume your data follow this format

xxx, yyy, zzz
space after comma

You may need to split your formula into 3 parts

=COUNTIF(F:F,"ZIKA,*")+COUNTIF(F:F,"*, ZIKA")+COUNTIF(F:F,"ZIKA")

The first part will count those start with ZIKA, second part count those end with ZIKA, last we should count those only with ZIKA

Prisoner
  • 1,839
  • 2
  • 22
  • 38