Conditional Sum IF

Platform discussions
,
1

I’m trying to create a function that references a dropdown column and assigns a number when a specific dropdown option is selected. Each dropdown options has it’s own number value. The function needs to identify the dropdown and sum all the selected options. Which is working, but I want to account for when the dropdown is empty and assign a number to that. To keep it simple here is an abbreviated version of the formula.

IF(SEARCH(“Self-Directed Learning”,{Deliverables})>0,6.5,0)+
IF(SEARCH(“Podcast”,{Deliverables})>0,8,0)+
IF(SEARCH(“”,{Deliverables})>0,2.75,0)

If the dropdown includes Self-Directed Learning in Deliverables add 6.5. If it includes Podcast add 8. These arguments work, but the if Deliverables is “” or empty, it adds 2.75 to the sum regardless of if it is empty or not.

For example, if it is a Podcast ONLY it adds 8 and 2.75 (from the " " argument) giving a total of 10.75. It should only return 8. What do I need to update?

2

Hi Drew,

Try:

IF(SEARCH("Self-Directed Learning",{Deliverables})>0,6.5,0)+
IF(SEARCH("Podcast",{Deliverables})>0,8,0)+
IF({Deliverables},2.75,0)

 

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

Thank you for the help, but unfortunately that is yielding the same result. It will still add 2.75 to the sum if a deliverable is found. Oddly enough for those with no deliverable it is assigning 0.

4

For sure, it will. I misunderstood your request.
What exactly are you trying to achieve with?

 IF(SEARCH(“”,{Deliverables})>0,2.75,0)