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

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’

Haven’t tried but something like COUNT(SELECT(TABLE[Values], [Values]=[Value I want]))? This would only work if you can somehow know what you want to count and wouldn’t be useful for something like finding all duplicated values.
(Disclaimer I have not attempted this solution and it might not be useful for your case)

Thanks for the reply. It’s a text list and not a reference list. I also don’t want to use SELECT / FILTER in a virtual column. It’ll just slow things down.

Select does not slow apps down much unless dealing with very large datasets, in the 10k+ range.
I do not believe there is currently a method that allows you to know how many occurrences of an item there are in a list. What is your use case for counting an item in a list? Might be another way to accomplish the goal.

What about this?

count(LIST(“A”, “A”, “B”, “B”, “A”, “C”, “D”) )-len(substitute(substitute(text(LIST(“A”, “A”, “B”, “B”, “A”, “C”, “D”))," , ", “”), “A”, “”))

I think you can twist this expression a bit to make it dynamic rather than static

There is no quick and easy way to do this. It would be significantly faster and more efficient to do with with Refs and SELECTs.

1 Like

Interesting, this would only work on string items length 1 though right?


Depending on the complexity of the individual items within the list, data loss could occur because of smashing all the letters together and then accidentally substituting from the combined string.

Edit: Well, not data loss. Just incorrect results.

I’m just in line with his query not assuming complex case.

This can be done using EXTRACTMENTIONS() if you add an “@” to the beginning of your pattern.

COUNT(EXTRACTMENTIONS(SUBSTITUTE([ColumnToSearch],"Pattern","@Pattern ")))

Screen Shot 2020-07-16 at 5.35.15 PM


I was staring at these while writing my first reply thinking “if only there were a way to use extract here somehow”.

Now, does it work if there are spaces in the pattern? Or would the spaces need to get subbed out?

You can search for a pattern with a space, and then just replace it with an underscore so the EXTRACTMENTIONS() part will work.
COUNT(EXTRACTMENTIONS(SUBSTITUTE([ColumnToSearch],"Patt ern","@Patt_ern ")))

Keep in mind, we’re not actually writing the substitute back to the sheet. Just changing the pattern to count it.


That works. Thanks.

Some kind of counting an item from a list should be build in expression. :frowning:

1 Like

Ok. Yeah. That’s what I was referring to. You have to sub the space in the pattern one way or another for it to work.

Edit: or, rather, what I mean is, if your data has, or might have, spaces in it, you need to account for them in some way in regards to using this extractmentions method.

1 Like

It looks like some guy name Patt Ern is getting notifications. :joy:
Screen Shot 2020-07-16 at 5.52.19 PM

The forum made it bold and tagged it lol.

1 Like

There is a bit simper solution:


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.


Since @GreenFlux’s and my solutions are based on SUBSTITUTE, the search text must be unique. Otherwise, it won’t work.

It will work for my particular case.

So I will create a feature request.
Thank you guys for the helps.

1 Like

is the feature request.

      SUBSTITUTE(","&[TextList]&",", ","&[Pattern]&",", ",@"&SUBSTITUTE([Pattern], " ", "_")&",")


I feel like this should work pretty much always.

Edit: updated. All you need to do is include the delimiter in the search string and remember to keep it in the replacement string.

I made this real quick as an example. Test data has a mix items with spaces and not. Example also passes the pen/pencil test case, and last item case.