Help

Count the entries in a lookup field?

25643 48
cancel
Showing results for 
Search instead for 
Did you mean: 
jezburrows
5 - Automation Enthusiast
5 - Automation Enthusiast

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

48 Replies 48
Mac
8 - Airtable Astronomer
8 - Airtable Astronomer

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

jezburrows
5 - Automation Enthusiast
5 - Automation Enthusiast

@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
8 - Airtable Astronomer
8 - Airtable Astronomer

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

https://airtable.com/shrmfrZgcN825AH4P

jezburrows
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

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

Screenshot 2019-03-13 at 4.14.50 PM.png

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

Mac
8 - Airtable Astronomer
8 - Airtable Astronomer

Jez,

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

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

Loic_Sanchez
6 - Interface Innovator
6 - Interface Innovator

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.

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.

@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:

Screenshot 2019-04-14 at 06.21.06.png

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