Counting unique occurences in Airtable linked fields

Airtable is great, but the reporting facilities are limited. Say we have a table of class attendances in Airtable. Each class has a date, a subject and a list of linked attendees (shown here using letters instead of names):

date subject attendees
01/07/2021 English A,B
01/07/2021 Cookery C,D
01/07/2021 French A,E,F,G
02/07/2021 English A,B,H


We want to find out:

  • how many unique attendees there were for English classes in July 2021; and
  • how many times each of these people attended English

There is no practical way to do this in Airtable that I am aware of. But we can easily do it in Easy Data Transform.

  • First we need to create a filter in an Airtable grid view of the table:

  • Then we need to download the filtered data as a CSV:


  • Then we can transform the data into the form we need it, like this:

  • input the CSV.


  • use Split Col with Into set to Rows to move each person’s attendance onto a unique row.

  • use New Col to add a column of 1s that we can sum.


  • use Unique to keep unique names and sum the number of times they occur.

  • use Sort to order by number of attendances.
  • use Remove Cols get rid of cols we don’t need.


  • output the results to a CSV file.

Example CSV input file:

class-attendance.csv (308 Bytes)

Example .transform file:

airtable.transform (3.5 KB)

1 Like