Count the entries in a lookup field?

#1

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

#2

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

#3

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

#4

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

https://airtable.com/shrmfrZgcN825AH4P

#5

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.

#6

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.

#7

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.

#8

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.

#9

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.

#10

@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

#11

Thanks to @Mac for figuring this one out. For anybody with a similar issue, it required a Rollup using SUM(values).