Count the entries in a lookup field?

[jezburrows]

Hey there,

Is there some way to count the number of entries in a Lookup field in the same table?

I have a Lookup field called ‘Roles’—it references another field on the same sheet called ‘Movies’ and spits out a list of acting credits associated with those movies. I need to count how many credits there are, but the ‘Roles’ field doesn’t appear as an option in the Count drop-down. Is there some other way to do this? A formula maybe? Any help appreciated! Thanks!

Jez

[Mac]

You should be able to use a roll-up field and COUNTALL to grab the number of linked records.

Airtable - Support

Rollup field reference

For an overview of rollups, check out the Guide to Formula, Lookup, Count, and Rollup Fields. A rollup field aggregates specific cells from records that are linked to another field in your table. ...

[jezburrows]

@Mac Forgive me if I’m misunderstanding, but I don’t want to reference fields on another table—I just want to count the number of entries in a Lookup field on the same sheet.

[Mac]

@jezburrows so this isn’t what you’re looking for?

https://airtable.com/shrmfrZgcN825AH4P

[jezburrows]

I’d love to include a link to the specific table I’m talking about, or even include a screengrab, but for some asinine reason I’m not allowed to.

I want to simply count the number of entries in a Lookup field that which collects all the roles belonging to a particular writer. This table is the only place where these roles are collected together by writer, so using a Rollup field to reference another table won’t work.

[jezburrows]

Ah, looks like I’m able to post stuff now. Here’s the table:

I just want to count all the entries in the ‘Roles’ field.

[Mac]

Jez,

Would you be willing to share a copy of your base through a private link?

Airtable - Support

Creating a base share link or a view share link

As an alternative to adding individual people as collaborators, you can create a read-only share link that allows anyone with the link to view (but not edit) an Airtable base or a specific table vi...

I can copy your base into my workspace and see if I can get it to work for you.

[Loic_Sanchez]

Hi @jezburrows,

What is the formula currently in ‘Role Count’?
It looks to me that using COUNTALL({Roles}) should get you what you’re looking for.

[jezburrows]

Hey @Loic_Sanchez,

Sadly COUNTALL({Roles}) just lists the number 1 in every single cell instead of how many individual roles there are in the Roles lookup column.

[JonathanBowen]

@jezburrows - can you share your base or explain how the roles lookup is configured (or where it is coming from)? I tried to mock your table:

In my case, Roles is a field in the Movies table and is shown in this table as a lookup. Count of Roles then works using:

COUNTALL(Roles)

But…my lookup is displayed as a comma-separated list/array - yours shows individual “pills” for each role, so you must have this set up in a different way. If we can understand this, might be able to help.

JB