How can I count the number of multi-select items selected?


#1

I’m trying to work out how I can have different members of a team view a spreadsheet of ideas (each row is an idea) and have them each vote on whether they like the ideas. I want to then show an updated tally of the number of votes each row gets.

I was thinking maybe I have a multi-select column with the names of each team member. If you like an idea you add your own name to this selection. I’d then want another column which is a number, which I was thinking I could have a formula to count the number of items selected in the multi-select field.

So I’m not sure what formula would work there. Or if perhaps there’s a better way of approaching this?

Thanks.


#2

Instead of using a multi-select field for people’s names, create another table with a record for each team member, and their name in the pimary field.

In your “Ideas” table (if I’m understanding what it is correctly), in place of the multi-select field, use a “Link to another record” field, and have the voters add their name by linking it — the interface will still look and function very similarly to a multi-select. But this will allow you to use a “Count” field to count the votes per idea, instead of having to come up with a formula to do it.


#3

I think @Jeremy_Oglesby has the right approach for your problem — but for completeness’ sake, and to help anyone who hits this thread based on its topic, there’s an easy way in Airtable to count the number of [whatevers] in a list: Selected multi-selects; collaborators; lookups; whatever. It’s based on the fact that Airtable returns such lists as arrays — and it makes such arrays accessible to formulas as a string of items, separated by commas.

So, to count the number of selected options in a multi-elect field, simply count the number of commas and add 1

Note: Two caveats apply: First, there can’t be any commas embedded in your [whatevers], so no entries referencing 'Alfred, Lord Tennyson' or similar. Second, the 'add 1’ part kicks in only if the returned string isn’t empty. The formula takes into account the second caution; the first has to be enforced by the base creator.

So, to count your multiselects, try this formula:

IF(
    LEN(
        {MultiselectField}
        )=0,
        0,
        LEN(
            {MultiselectField}&','
            )-LEN(
                SUBSTITUTE(
                    {MultiselectField},
                    ',',
                    ''
                    )
                )
    )

  1. Clever hack courtesy of @Simon_Brown, who, like some masked stranger from days gone by, rode into Airtable Community briefly one morning and left behind him this silver bullet of a reply.

#4

Thank you both, very useful :slight_smile: