Solved: Count how many times an item appear in a text list... - Page 2

Steven_Aung · 07-16-2020 01:55 PM

Say I have a list LIST(“A”, “A”, “B”, “B”, “A”, “C”, “D”) and want to know how many “A” in the list. How can I do that with expression?

Few things I found out so far:

Using ‘-’ sign will remove any duplicate from the list. LIST(“A”, “A”, “B”, “B”, “A”, “C”, “D”) - LIST(“A”) will give me [“B”,“C”,“D”]. “B” only appeared one time.
INTERSECT remove duplicates too. INTERSECT({‘A’, ‘A’, ‘B’}, {‘A’, ‘A’, ‘C’,‘C’}) will return just ‘A’

Steven_Aung

There is a bit simper solution:

IF(ISBLANK([LIST]), 0, COUNT(SPLIT(SUBSTITUTE([LIST] ,"SEARCH_TEXT","@"), "@"))-1)

If “SEARCH_TEXT” is not in the list, the split will still return one item list. So ‘-1’

** Assuming that 'SEARCH_TEXT" must be unique. If any longer text having ‘SEARCH_TEXT’ as sub-string, this solution will not work.

Count how many times an item appear in a text list (not ref list)