Non-typical dynamic count

[Andrey_L]

Hi everyone, Please advise how to create a formula for a field (let’s call it “CityCount”), which would automatically display the number of records in this table which have the same value in the field “City” as a current record?

Here is an example: there are 3 records in the table with City=London, thus each of the record should have CityCount=3. If I add one more record with City=London, then each of such records would get updated with field CityCount=4.

Any help or ideas appreciated!

[kuovonne · Accepted solution]

Welcome to the Airtable community!

In order to do a count you would need a linked record field.

Turn your {City} field into a linked record field. (Just change the field type in the field configuration screen, and tell Airtable that you want a new table). Then you can use a {Count} field in the table of [Cities] to get the total number of cities. Finally, pass that count back to the original table using a rollup field with the formula AVERAGE(ARRAYUNIQUE(values)).

[Alexey_Gusev]

HI,

there is no ‘vertical connection’, and records ‘don’t know each other’, so the only way to maintain ‘totals’ in actual state in formulas described by @kuovonne.
but in general, it depends on why you need this number, for constant use or just check duplicates from time to time.
you may use grouping in view,
you may view a list of duplicates and their count sorted descending, by ‘customize’-‘single select’(don’t press ‘save’).
you may link to empty table, copy column to linked field and get counts via lookup in separte text row.

[Andrey_L]

Thanks, that works! Although I thought it would be possible to solve without creating an additional table.

[Andrey_L]

It’s for constant use, because I have some other logic dependent on whether there is only one or more than one record associated with a given City.