Help

Updating a formula to accommodate 4 or more items in an array

1682 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Stuart_Rice
4 - Data Explorer
4 - Data Explorer

Hi All:

We are currently using the following formula to calculate course enrollment based on the values in an array.

SUM(IF(VALUE(LEFT(ARRAYJOIN(Enrollment),FIND(’,’,ARRAYJOIN(Enrollment)),-1))=0,VALUE(Enrollment&’’),VALUE(LEFT(ARRAYJOIN(Enrollment),FIND(’,’,ARRAYJOIN(Enrollment)),-1))),VALUE(RIGHT(ARRAYJOIN(Enrollment),FIND(’,’,ARRAYJOIN(Enrollment))-1)))

This is able to successfully calculate up to three items, but fails when four are in the Enrollment column. Does anyone have a suggestion on how to amend this formula to work with 4 or more items in the array?

Thank you!
Stuart

5 Replies 5
Zollie
10 - Mercury
10 - Mercury

As you’re finding out, Airtable formulas are lacking meaningful array support. You might have more luck if you’re able to leverage a rollup field like this user was.

Otherwise, early access is available to the Scripting block (which is in beta).

Yes - two words - Script Block.

I’m pretty sure this is one of [possibly] many hundreds of ways to overcome the roadblocks of the lacklustre formula feature set.

While the code to do this in javascript will not likely be simpler, it will scale to 4, 10, or perhaps 100 items in the array.

Unfortunately, roll-ups won’t work because the field in question is a look-up field.

Thanks! I’ve requested early access to it.

@Stuart_Rice,

Feel free to share a few sample enrollment values here in the meantime. I might be able to get you started with some essential code.