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

[Paul_Warren]

@Justin_Barrett, You are a champion among men, thank you!! This worked perfectly. I love your comment " It might seem redundant because we can already see those in the linked records, but this works, so just hide the field once you’re done." This happens so much, I have over 50 fields which are hidden in all views and some with over 90! Haha, thanks a million!

[Stephanie_Lynn_]

Okay so I’ve read over and over this thread trying to discern the solution to my (rather urgent) problem and I’m still stumped.

I made a tiny version of my (very large) base tracking descriptive data about an archival radio collection, with just a handful of records. This is an Editor-level access invite link: https://airtable.com/invite/l?inviteId=invedrpRqDjTbNJiM&inviteToken=276a551d2a2e59db96920a363477c39...

‘Programs’ - individual radio programs
‘Subject(s)’ - Linked field. Programs contain multiple subjects.
‘Theme(s)’ - Linked field. Subjects are connected to one or more Themes.

I need a field in the Theme(s) table displaying HOW MANY UNIQUE PROGRAMS HAVE BEEN CONNECTED TO THAT THEME, VIA THEIR ASSIGNED SUBJECTS. The reason this is important is because as I keep curating this collection, I can just assign a SUBJECT to a theme, and all the programs with that subject automatically inherit the correct Theme(s). Otherwise I’d have to assign Themes individually to 5,000+ programs, which is laboriously prohibitive.

I tried creating a Count field in Subject(s) which displays how many programs have that Subject assigned, then created a Lookup field in Theme(s) displaying that count, but it showed a list of count values separated by commas. Then I tried creating another Rollup column in Theme(s), linking to the Subject(s) table, with the aggregation formula SUM(ARRAYUNIQUE(values)). This did not work either.

Does anyone know the answer to this?? We’ve got a whole mess of curators looking at a read-only table of all Themes in the collection, and I need that count to work!

[Paul_Warren]

Hello Stephanie!

Welcome to the Airtable community. It is an awesome place :grinning_face_with_big_eyes:

I’ve taken a stab at it. I had to implement @Justin_Barrett’s suggestion. Create a lookup field in your subjects table to lookup the linked program’s names. I call this field: “Linked Programs - Look up”. Then create a roll-up field in the Themes table which does a look up on the linked subjects and aggregates values from the “Linked Programs - Look up” with ArrayJoin(ArrayUnique)). I call this field “Linked Programs”

Finally, create a formula to count the length of the rollup output:
IF({Linked Programs}, LEN({Linked Programs}) - LEN(SUBSTITUTE({Linked Programs}, ‘,’, ‘’))+1)

I hope this helps! Here is a link to an edited version of your base. For some reason, I couldn’t edit the link you sent, so I had to duplicate it: https://airtable.com/invite/l?inviteId=invQYgG9iIOYm2u39&inviteToken=3c7e7853cf62c44c1892b97f69faacb...

[Stephanie_Lynn_]

@Paul_Warren You just saved me!! I consider myself an advanced user of Airtable, but may have to reconsider the term ‘advanced’ in light of this group’s ingenuity.

I still don’t quite understand how your final formula works, but I’m glad it does and it helped me out a lot.

In case you’re curious, here’s the (read only) link displaying the implemented change. Now all Themes display a proper count of matching programs. FWIW, the ‘URL: Linked Programs’ column displays a read-only link to the programs matching each theme :). https://airtable.com/shrJMBvdPPsaWuBB0

[Justin_Barrett]

Stephanie_Lynn_Sapie:

I still don’t quite understand how your final formula works, but I’m glad it does and it helped me out a lot.

Here’s the breakdown. First, it looks to see if {Linked Programs} contains anything.

IF({Linked Programs}, ...

If that’s true, it takes the length of that field’s contents, subtracts that same length after replacing all commas with empty strings, then adds one. In short, it’s giving you a number that’s one greater than the number of commas separating the items. If you have one comma, it’s between two items; if you have five commas, they’re separating six items; etc.

[Mei_Yi_Yeap]

This is the clearest explanation I’ve gotten so far. Thank you!!

[Natalie_Zdan]

@Paul_Warren @Stephanie_Lynn_Sapie
I was so excited to find your posts since it seemed like that formula would do exactly what I needed it to. When it didn’t work, I opened Stephanie’s read-only link to see if I could somehow see the formula there, but it looks like the formula isn’t working there anymore either… Says there’s a configuration area and there are no counts appearing in the column. Any idea why it might have broken? (See screenshot below)

I too want to perform a count of the number of items rolled up using the ARRAYUNIQUE(values) aggregation function. Would be very grateful for any guidance!

Formula I’m trying that won’t work, next to rollup field that uses ARRAYUNIQUE(values):

Broken formula from Stephanie’s read-only:

[Paul_Warren1]

Good Morning Natalie!

I am not certain what is broken on Stephanie’s read-only link, but after some head-scratching I figured out the issue with the formula above. Airtable ONLY supports a specific type of quotation mark. I’m a little fuzzy on the nomenclature, but I know @ScottWorld has lodged several complaints about this frustrating issue.

Simply delete the quotation marks and type them again with your keyboard and the formula should work. I’m not sure why they are incorrect in my previous post. I think the code below should work.

IF({Sites (from Volunteering Schedule)},LEN({Sites (from Volunteering Schedule)})-LEN(SUBSTITUTE({Sites (from Volunteering Schedule)}, ',', ''))+1)

I hope that helps!

Also, this works with Look-up fields. I’m not sure how it works with a roll-up field

---

*EDIT to put formula in a code block, per @Justin_Barrett’s suggestion.

[ScottWorld]

That is correct — quotation marks in formulas must be straight quotes, not curly quotes.

At my request, Airtable has updated this support page with this information:

Airtable Support

Common formula errors and how to fix them

When building formulas or using one of Airtable's computed fields, you may run across an error. These error codes include #ERROR, invalid formula, NaN, circular reference, and infinity. Below are m...

[Justin_Barrett]

Paul_Warren1:

I’m not sure why they are incorrect in my previous post.

They’re styled because the default behavior in this forum is to apply styling to all quotes. To display non-styled quotes, select the formula/code in the post/comment editor and apply the “preformatted text” style using this button in the toolbar: </> That will turn your text into this, which uses non-styled quotes:

IF({Sites (from Volunteering Schedule)},LEN({Sites (from Volunteering Schedule)})-LEN(SUBSTITUTE({Sites (from Volunteering Schedule)}, ',', ''))+1)