Count number of occurrence of string in google sheet

Question

I have a column, which has data like this :-

C29,C22,C345
C292,C345,C456
C456,C29,C453

Now I want to find number of occurrence of C29, and I am using this formula now:-

COUNTIF(I9:I11,"*C29*")

But it is returning 3, as it also take C292 in an account, how can I set a formula to match only C29?

Thanks

JvdV · Accepted Answer · 2022-06-01 07:39:28Z

1

You can try:

Formula:

=SUMPRODUCT(ISNUMBER(FIND(",C29,",","&A1:A3&",")))

Or, less verbose:

=SUMPRODUCT(REGEXMATCH(A1:A3,"\bC29\b"))

Or, even less verbose:

=SUMPRODUCT(SPLIT(A1:A3,",")="C29")

answered Jun 1, 2022 at 7:33

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Solar Mike · Accepted Answer · 2022-06-01 10:44:07Z

0

So, two options:

use sum(), if() and iferror() with find() as shown down in rows 11 to 13, formula is:

=SUM(IF(IFERROR(FIND("C29,",A11:A13,1),0)>0,1,0))
if you have the room, the do Text-to-Columns and get each data element into its own cell. Then extract the numbers, either replace the C with nothing or mid() as shown. Then count the 29.

answered Jun 1, 2022 at 6:59

Solar Mike

8,4255 gold badges23 silver badges34 bronze badges