Mar 11, 2019 08:20 PM
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
Mar 11, 2019 08:51 PM
You should be able to use a roll-up field and COUNTALL to grab the number of linked records.
Mar 11, 2019 09:15 PM
@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.
Mar 12, 2019 05:16 AM
@jezburrows so this isn’t what you’re looking for?
Mar 13, 2019 04:11 PM
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.
Mar 13, 2019 04:15 PM
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.
Mar 13, 2019 05:51 PM
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.
Mar 14, 2019 06:44 AM
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.
Apr 13, 2019 03:47 PM
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.
Apr 13, 2019 10:25 PM
@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