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_Warren1]

@Justin_Barrett, thanks!

[Natalie_Zdan]

Thank you @Paul_Warren1 and @Justin_Barrett

[Sean_Lake1]

For me the field I require(the lookup field) isn’t an option on count nor rollup, so how am I to get a count if it’s not an option?

As you can see the lookup field that I want to get a count of total number in field, isn’t an option.(Attendees_Autogenerated)
Any ideas?

[kuovonne]

Sean_Lake1:

For me the field I require(the lookup field) isn’t an option on count nor rollup, so how am I to get a count if it’s not an option?

Base your count field on the same linked record field that the lookup field uses.

[Sean_Lake1]

The attendee field has multiple entries ,comma separated, so the field it’s based on wouldn’t return the same number.

Really, there’s no simple formula for this?

[Sean_Lake1]

=LEN(TRIM({attendee}))-LEN(SUBSTITUTE(TRIM({attendee}),",",""))+1

[Sean_Lake1]

Yup, doesn’t work nor does a rollup, nor count field as the field we’re drawing from is a lookup field. Lookup fields seem difficult to access, except for maybe array related formulas.

[momentsgoneby80]

@Sean_Lake1

Assuming I understood the issue; try this:

IF({attendee}&'',LEN({attendee}&'')-LEN(SUBSTITUTE({attendee}&'', ',', ''))+1)

LEN() returns the lenght of a string, not an array, but by adding &’’ at the end of a field name you get arround that issue.

[Justin_Barrett]

Sean_Lake1:

Lookup fields seem difficult to access

Absolutely, largely because many users don’t realize that a lookup field is returning an array (i.e. a collection of values), not a single item (i.e. a string, a number, etc.). The solution mentioned by @momentsgoneby80 solves this problem. By concatenating the array with an empty string, Airtable converts the array to a string, and then processing by string functions works as expected.

[kuovonne]

Sean_Lake1:

Yup, doesn’t work nor does a rollup, nor count field

You cannot base a count or a rollup off of a lookup field. You need to base it off of the same linked record field that the lookup field uses, and set the same conditions. If your {Attendees_Autogenerated} field is based off the {Attendees} field, then the count or rollup field needs to also be based off of the {Attendees} field.

Also, is it a lookup of an editable field, or a lookup of a lookup? A lookup of a lookup needs to be flattened before counting. COUNTALL(ARRAYFLATTEN(values))