Counting words in a lookup field

[Claudio_Brenni]

Hi Airtable Community!

I’m developing a base to manage a bike polo championship.

So far so good, I could implement all the function that are required, but I’m stumbling on a formula that I can’t get to work.

On this table

I want to get the final score for both Teams. As for shown with the example on the last column which it was achieved with a Count field and a filter with the name of the Team ‘Test1’. That is not a solution for me, because what I want is to filter the count based on the name of the column ‘Team 1’ and not on a particular team name.

I’ve tried to combine IF () with FIND(), IF(FIND({Team 1},{Which team scored?}),1,0), but then I get only a 1 or a 0. Does anyone have any idea on how I can then count the frequency of the string in the column ‘Team 1’ and than count it or Sum it? I’ve tried to add SUM() or COUNTA() in the formula, but unsuccessfully.

I would very grateful if anyone can help me :winking_face:

many thanks,
Claudio

[Dimitris_Goudis · Accepted solution]

Hi @Claudio_Brenni,

I am not sure that you can filter the results you request based on the column name because they are unique fields…

As your purpose is to display the final score, you could create 2 fields in score table each of which will display the goal numbers of the every team separately. Then you can create the look up function that will request the results in the final table you have in the picture.

Does it work for you?

Thanks
Dimitris Goudis

[Claudio_Brenni]

Many Thanks Dimitris!

A I’ve just implemented your solution and it is working! Thanks a lot for the tip!

Best,
Claudio