Count the entries in a lookup field?

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

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.

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

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:

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

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)

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

@Justin_Barrett, thanks!

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

Thank you @Paul_Warren1 and @Justin_Barrett

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?

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

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?

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

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.

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

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.

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))

I really wish Airtable’s forums were more like StackOverflow…

I’m having this issue and I’m sure I faced the same thing a few months ago, can’t can’t seem to find exactly how to apply it to my use case…

Also can’t seem to figure out how to make the myriad of suggestions work for myself.

Hi Nachliel, thank you for reply! Unfortunately this is not where the issue comes up for me.

I have the following tables:

1. Student


2. Course


3. Lesson

A lesson belongs to a course, so I have a junction table:

4. Course-Lesson

A student is enrolled into a course, so I have a junction table:

5. Student-Course (which has a record for each course into which the student is enrolled.)

A student completes lessons, so I have a junction table:

6. Student-Lessons (which is populated only with records of ‘lessons completed’)

My goal:

• 
  

  Firstly, on the Course table, I want to list the total number of lessons associated with a course. This I can successfully achieve with your recommendation of using ‘Count’ and a linked field.

  
  


• 
  

  Then, on the table, Student-Course, I want to be able to count the total number of ‘lessons completed’… for the associated course in that record.

  
  

But as for @jezburrows (in his post on Apr '19) a Count or Rollup here only produces the number 1.

If I attempt the solutions proposed by @Justin_Barrett @Paul_Warren then it produces the total number of lessons completed for that student, on all courses they are enrolled in, not just the course associated with that record in Student-Course.

I hope all this makes sense!

Thank you so much for your assistance.

Pierre

A rollup dose calculations on fields of records that are connected to a specific record in the same table.

So in your case assuming this is your Student-Lesson Junction table and you want to count the sum of lessons a student attended (3 courses - two lessons per course, and Jake Baker missed one lesson of Course C on the 13th, and one lesson of Course B on the 14th):

In order to count the checked Attendance field in the Student-Course Junction you will need to link each record to the Student-Lesson Junction table records, so the Student-Course Junction table will look like this:

And now that we can access the Student-Lesson Junction we can count how much lessons were attended like this:

Thank you @test_google for taking the time to create this demo.

This works! But… it appears to me that there should be a more elegant solution, without the need for:

a.) the extra field (Attendance) and

b.) the extra records that need to be added to Student-Course every time a lesson is completed.

Some context:

I have added all lessons to the table, Lesson.

I have added all these lessons to their associated Course in Course-Lesson Junction.

And student Jo Doe is already enrolled only in Course A. (Maybe Jake Baker is enrolled in both Courses A and B.)

I currently use Zapier to ‘listen’ to my LMS for every time that a lesson is completed, and then when this event occurs it adds a record to Student-Lesson Junction.

This means that all the information required to calculate lessons completed, by student (Jo Doe) for Course-A already exists. (Remember, in my base Student-Lesson Junction is populated only with completed lessons, so the ‘attendance’ field seems superfluous.)

–

The solution that you propose would require of me to also do the following:

1.) Add a field to Student-Lesson Junction for ‘Attendance’ (in my case ‘Completed’)

2.) Set Zapier to not only add records to Student-Lesson Junction, but also to Student-Course Junction at the same time.

Is this correct? Because this seems unnecessary to me.

Would it not be more elegant to use some type of formula that aggregates the tables Student, Course and Lesson (with the help of junctions) and counts the total number of records?

I hope this makes sense. Maybe I’m completely delusional

I,ve faced the same problem and I found a easy formula solution for it:

If you want to, for example, count the number of dates (or any data by the number of entries) in a lookup field, this works:

I'm in the same boat.  When I attempt to count the number of "entries" of the Attendees(based on a lookup from 

This field is not available to count FROM.  

Thank you for figuring this out!! Huge savior for creating my inventory checkout system.