Formula with dropdown column

Platform discussions
, ,
1

I’m using a multi-select dropdown column using days of the week. One person can select the day(s) and how many people will attend. I’m trying to figure out how to automatically make a sum of people that will attend on ‘Monday’, then a separate column with a sum of people that will attend on ‘Tuesday’, etc.

I have tried- IF(SEARCH(“Monday”,{Dropdown#Labels},{Number of Attendees}). This didn’t work.
I’m looking for something that will search for (Monday, Tuesday, etc) in my dropdown, then insert the number from ‘Number of Attendees’ column. That way, my sum at the bottom will be a grand total of attendees for Monday. Then the next column will be a grand total for Tuesday… and so on. In the end, I would have 7 Formula columns. Each one searching for a different day of the week.

Or is there an easier way to accomplish what I’m trying to do?

2

Hi Paul,

Try

IF(SEARCH("Monday",{Dropdown#Labels})=1, {Number of Attendees},"")

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

3

This formula works for my first formula column (Monday), but when I use the same same formula (changing the search to “Tuesday”) for a second column, then nothing populates. This item- the attendees are present Monday, Tuesday, Wednesday.

But… when i submit another form where the attendees will be present Tuesday, Thursday, Friday. Then the formula populates my Tuesday column, but not the other days of the week.
Any ideas to fix this?

4

Replace =1 by >=1

What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

5

Perfect. That solved the issue.