- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2019 07:16 AM
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.
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 24, 2020 05:48 AM
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
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2019 07:39 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2019 09:13 AM
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},
',',
''
)
)
)
- 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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 30, 2019 12:41 AM
Thank you both, very useful :slightly_smiling_face:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 17, 2020 09:19 AM
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:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 18, 2020 07:48 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 24, 2020 04:21 AM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 24, 2020 05:48 AM
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
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 24, 2020 06:42 AM
Thanks so much :raised_hands:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oct 07, 2020 01:18 AM
Thank you so much. It worked like magic :sparkles: