count the occurrence of a text

Developers
1

I want to count the occurrence of a text in the column of a subtable. How can I achieve it using formula? If the formula is not capable of it, how about JS Workflow?

1 Like
2

Dear @batoctoy321
I have thought of your question, and IF I have understood correctly, we have a Sheet, this Sheet has let’s say some columns (e.g 10) and in one of those columns you want to COUNT the EXISTENCE of a piece of TEXT (of a String).

There is a STRING Formula in Ragic called FIND

================================================================

FIND(find_text,within_text,[start_num])
Returns the starting position of a specified text string within another text string (starting from 1). Returns 0 if not found.
The find_text argument is the string to search for,
within_text is the text string to search within,
and [start_num] is an optional parameter that specifies the starting character position for the search (default is 1). For example, if A1 is “ABCD”, FIND(“BC”, A1) returns 2, FIND(“EF”, A1) returns 0, and FIND(“C”, A1, 3) returns 3.

Returns 1 if the specified substring (find_text) is empty. Refer to this example for details.

==============================================================

Now since this Formula returns numbers, when It FINDS the text successfully the Result will be >0
So you can Combine that FIND formula with a COUNTIF formula - in order to COUNT how many times the FIND formula gave you Nums > 0.

For example - you apply FIND in Column X and you get 7 different numbers > 0 , each number indicating at what point in the oevarll text - your text starts, - this means you got 7 times that your text appears in the column.

==========================================================
COUNTIF

You can find about COUNTIF here About Conditional Formula (COUNTIF)

=======================================================
How I would try to do it

I would go to the Sheet I am interested /> Then Create an extra column called “Test” example /> Apply the FIND in the extra column “Test” /> referencing the cells in the Column X that I am interested /> by applying the formula I would get one number for each row of column X in the column “Test”.
I would then apply COUNTIF to Column “Test” asking IF(value in Column “Test” is > 0) to get the number of occurences of the text string I am looking for.

====Hope this can be of help ====
Best Regards
IliasVlachos