Skip to main content
Solved

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

B

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.

Best answer by Justin_Barrett

EYES_Internatio wrote:

Hi Justin,
Thank you for your quick response! I have never taken part of a forum like this, and am really bad at formulas in general, so really appreciate it!

I tried my best to implement your suggestion but it is not working.

If for instance, I have the following formulat that counts the number of multiple selects selected.
Like this:
IF(
{All Events Attended},
LEN({All Events Attended}) -
LEN(
SUBSTITUTE(
{All Events Attended},
“,”,
“”
)
) + 1
)

But I would like to now add a column next to this, counting multiple selects that include the word “2019”, or counting specific multiple select options for e.g any results that include either/or/ “Hamburg 2019”, “Bali 2019” “Ski Trip 2019”
How would I implement that?

Thanks so much!


The formula I wrote above will work to count all of the “2019” options once you change my generic field name to the one in your table:

(LEN({All Events Attended}) - LEN(SUBSTITUTE({All Events Attended}, "2019", ""))) / 4

Counting specific items makes the formula longer, but it’s doable:

SUM(
    FIND("Hamburg 2019", {All Events Attended}) != 0,
    FIND("Bali 2019", {All Events Attended}) != 0,
    FIND("Ski Trip 2019", {All Events Attended}) != 0
)

View original
    A
    1 person likes this
    • A
      Adam_Minich
    Did this topic help you find an answer to your question?

    9 replies

    J

    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.

    P
    E
    2 people like this
    • P
      Paolo_Perrone
    • E
      Elias_Gomez_Sai
    W
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • January 28, 2019

    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.
    pressGO_design
    B
    C
    17 people like this
    • pressGO_design
      pressGO_design
    • B
      Burner
    • C
      Christiaan_Huiz
    • A
      Antoine_Morin
    • S
      Soundarya_Balas
    • G
      Gabrielle_Hurte
    • E
      Emily_Hanzlick
    • R
      risa_mulyadi
    • B
      Brandon_Zobel
    • K
      Katie_Wood
    • T
      Tracy_Mehoke
    • D
      Daniel_Dancausa
    • G
      Gordon_Renouf
    • A
      Amy_Chou
    • J
      jord8on
    • S
      samuelleach
    • G
      Gormana
    B
    • Ben_Hayes
    • Author
    • New Participant
    • 1 reply
    • January 30, 2019

    Thank you both, very useful :slightly_smiling_face:

    E
    W_Vann_Hall wrote:

    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.

    Thanks! This helped me a lot.

    However, I have a question that would take this to another degree. If I want to count the number of multiple selects that for example include the word “2019” would that be possible? Or at least to have a formula that only counts the specific multiple select options, would that be possible?
    Hope anybody can give the answer to that! :grinning_face_with_big_eyes:

    J
    EYES_Internatio wrote:

    Thanks! This helped me a lot.

    However, I have a question that would take this to another degree. If I want to count the number of multiple selects that for example include the word “2019” would that be possible? Or at least to have a formula that only counts the specific multiple select options, would that be possible?
    Hope anybody can give the answer to that! :grinning_face_with_big_eyes:


    Take the length of the string returned by the multiple-select field, subtract the length of that same string with all instances of “2019” (or other text) removed via SUBSTITUTE(), then divide the result by the length of the thing you’re seeking (in the case of “2019”, 4).

    (LEN({Multiple select}) - LEN(SUBSTITUTE({Multiple select}, "2019", ""))) / 4

    On a side note, I recommend starting a new thread in cases like this rather than resurrecting an old one. If you wish to reference an older thread, you can include a link.

    E
    1 person likes this
    • E
      EYES_Internatio
    E
    Justin_Barrett wrote:

    Take the length of the string returned by the multiple-select field, subtract the length of that same string with all instances of “2019” (or other text) removed via SUBSTITUTE(), then divide the result by the length of the thing you’re seeking (in the case of “2019”, 4).

    (LEN({Multiple select}) - LEN(SUBSTITUTE({Multiple select}, "2019", ""))) / 4

    On a side note, I recommend starting a new thread in cases like this rather than resurrecting an old one. If you wish to reference an older thread, you can include a link.


    Hi Justin,
    Thank you for your quick response! I have never taken part of a forum like this, and am really bad at formulas in general, so really appreciate it!

    I tried my best to implement your suggestion but it is not working.

    If for instance, I have the following formulat that counts the number of multiple selects selected.
    Like this:
    IF(
    {All Events Attended},
    LEN({All Events Attended}) -
    LEN(
    SUBSTITUTE(
    {All Events Attended},
    “,”,
    “”
    )
    ) + 1
    )

    But I would like to now add a column next to this, counting multiple selects that include the word “2019”, or counting specific multiple select options for e.g any results that include either/or/ “Hamburg 2019”, “Bali 2019” “Ski Trip 2019”
    How would I implement that?

    Thanks so much!

    J
    EYES_Internatio wrote:

    Hi Justin,
    Thank you for your quick response! I have never taken part of a forum like this, and am really bad at formulas in general, so really appreciate it!

    I tried my best to implement your suggestion but it is not working.

    If for instance, I have the following formulat that counts the number of multiple selects selected.
    Like this:
    IF(
    {All Events Attended},
    LEN({All Events Attended}) -
    LEN(
    SUBSTITUTE(
    {All Events Attended},
    “,”,
    “”
    )
    ) + 1
    )

    But I would like to now add a column next to this, counting multiple selects that include the word “2019”, or counting specific multiple select options for e.g any results that include either/or/ “Hamburg 2019”, “Bali 2019” “Ski Trip 2019”
    How would I implement that?

    Thanks so much!


    The formula I wrote above will work to count all of the “2019” options once you change my generic field name to the one in your table:

    (LEN({All Events Attended}) - LEN(SUBSTITUTE({All Events Attended}, "2019", ""))) / 4

    Counting specific items makes the formula longer, but it’s doable:

    SUM(
    FIND("Hamburg 2019", {All Events Attended}) != 0,
    FIND("Bali 2019", {All Events Attended}) != 0,
    FIND("Ski Trip 2019", {All Events Attended}) != 0
)

    E
    1 person likes this
    • E
      EYES_Internatio
    E
    Justin_Barrett wrote:

    The formula I wrote above will work to count all of the “2019” options once you change my generic field name to the one in your table:

    (LEN({All Events Attended}) - LEN(SUBSTITUTE({All Events Attended}, "2019", ""))) / 4

    Counting specific items makes the formula longer, but it’s doable:

    SUM(
    FIND("Hamburg 2019", {All Events Attended}) != 0,
    FIND("Bali 2019", {All Events Attended}) != 0,
    FIND("Ski Trip 2019", {All Events Attended}) != 0
)


    Thanks so much :raised_hands:

    S
    W_Vann_Hall wrote:

    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.

    Thank you so much. It worked like magic :sparkles:

    Reply


    Most helpful members this week

    Terms & Conditions