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.