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

[ZVU_Uden]

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.

[kuovonne]

Welcome to the Airtable community!

You need to use a rollup field.

I wrote about how to do this on my website.

[ZVU_Uden]

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)

[Justin_Barrett]

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.