# I want to count the unique numbers in a lock up field

In {#Gew uit} i want to know how many times 25 (= teamnummer) is in {resul.gew.wedstr.uit}
I don’t know what formula to use.
I tried FIND({teamnummer}, {resul. gew. wedstr. uit}) but that counts all the nummers.

Welcome to the Airtable community!

You need to use a rollup field.

I wrote about how to do this on my website.

Thank you, but did doesn’t work.
If the {resul. gew. wedstr. uit} is 25,25,27,25,25 and the teamnummer is 25 then the result should be 4 (with your formula the result is always 1)

Start with @kuovonne’s suggestion to change the lookup to a rollup. For the aggregation formula, use this:

``````ARRAYJOIN(values)
``````

That will create a string that inserts a comma between each rolled up value. Using your latest example, it would look like this:

``````25,25,27,25,25
``````

For the `{#Gew uit}` formula, use this:

``````(LEN({resul. gew. wedstr. uit}) - LEN(SUBSTITUTE({resul. gew. wedstr. uit}, teamnummer, ""))) / LEN(teamnummer)
``````

Here’s what’s happening:

• We’re starting with the length of the rollup field’s output. With your latest example of `25,25,27,25,25` this gives us a value of 14.
• From that number we then subtract the length of that same rollup output with all instances of the desired number removed. The removal leaves `,,27,,` which has a length of 6. 14 - 6 = 8
• Finally we take that difference and divide it by the length of the thing we’re trying to find, which in this case is 2 characters. 8 / 2 = 4, which means that 25 is in that rollup field 4 times.
3 Likes

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.