Displaying counts of multiple and duplicate items after a Rollup field using ARRAYJOIN(values)

[Hairtable]

My Rollup field has (correctly) returned this array to me ARRAYJOIN(values) by looking at linked records (4 People) and their dietary requirements (dietary preferences are determined by a multiple select field):

None,Allergy to Nuts (other than Peanuts),Gluten-free / No beef / Allergy to Nuts (other than Peanuts),None

Another formula field formats this array into an easier to read arrangement:

1 x None
1 x Allergy to Nuts (other than Peanuts)
1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)
1 x None

What I would really like is to find a way to combine any duplicated items and show the correct count for them, such as:

2 x None
1 x Allergy to Nuts (other than Peanuts)
1 x Gluten-free / No beef / Allergy to Nuts (other than Peanuts)

Any advice on how to do this please? I want any formula to work dynamically with the content of the array, as this will make it easier to update the multiple select options without needing to rewrite any formulas that contain verbatim references to each and every multi-select text entry.

[Sistema_Aotearo]

Hiya! I think the rollup formula you may be looking for is:

ARRAYUNIQUE(values)

Let me know if this helps 🙂

[Hairtable]

Thanks @Sistema_Aotearo That function will return the 3 unique values, but it will not show me the quantity of each of those values.

[Sistema_Aotearo]

Ah, I see! There isn't a clean way to do this natively within one formula in Airtable. You're looking to summarize your data, on the table itself, which can get tricky! Here are some options to build it manually.

Make a count field for each of the options you want to be counted. Then make a formula to join them together in an easy-to-read manner like you've described. This can get a bit messy if there are quite a few options you want counted.

Otherwise, you might have to build scripts to summarize your data or use some of the charts and graphs functionality in the interface to summarize the data.