If(OR and Value formula issues
I have a check box that if checked I want my cell to be 0, but if it is not checked I would like it to sum up three other cells. What is wrong with my formula (Example #1)? I am also having an issue with values. If I have a formula that I am asking to return a "0" or "100" based on criteria (Example #2), I have to add another column to convert it to a value to do math with it (Example #3).
Example #1 =IF(OR([Four or more issues]@row = 1, "0", SUM([Value No Issue]@row + [Value Other Scoring]@row + [Value Final]@row)))
Example #2 =IF(CONTAINS("No", Status@row), "100", "0")
Example #3 =VALUE([No Issue Score]@row)
Best Answers

Three things:
Scrap the OR as it doesn't seem to be doing anything
Ditch the quote marks around the 0
Change the + in your sum formula to , to allow the SUM to work properly
End formula would be:
=IF([Four or more issues]@row = 1, 0, SUM([Value No Issue]@row, [Value Other Scoring]@row, [Value Final]@row))
If "Four or more issues" is checked, give value 0. If not, sum the other three cells.
Sample:
Is this what you're after, or have I misunderstood something? 🙂

Hi @Joey135,
Your formula is mostly sound, you just have a bracket in the wrong place (bolded), as you need to close the CONTAINS:
=IF(CONTAINS("No", Status@row), 100, IF([Four or More issues]@row = 1, 0, SUM([No Issue Score]@row:[Final Issue Scoring]@row)))
This should fix your issue.
Answers

Hi @Joey135,
For numeric returns, you don't need the quote marks around the number. For example, in the 2nd formula it would be:
=IF(CONTAINS("No", Status@row), 100, 0)
If you're then using this output in a further formula, it should function just fine as a number without needing anything additional adding in.
Hope this helps, but if you still have any problems/questions then let us know!

That worked on the value issue thanks so much @Nick Korna . Any suggestion on my other formula issue?
=IF(OR([Four or more issues]@row = 1, "0", SUM([Value No Issue]@row + [Value Other Scoring]@row + [Value Final]@row)))

Three things:
Scrap the OR as it doesn't seem to be doing anything
Ditch the quote marks around the 0
Change the + in your sum formula to , to allow the SUM to work properly
End formula would be:
=IF([Four or more issues]@row = 1, 0, SUM([Value No Issue]@row, [Value Other Scoring]@row, [Value Final]@row))
If "Four or more issues" is checked, give value 0. If not, sum the other three cells.
Sample:
Is this what you're after, or have I misunderstood something? 🙂

@Nick Korna Within the same project I have the below formula. I want it to look at a cell and if it is No than report a 100, if there is no "No" then look at the next column and look for a 1, if that criteria is not correct, then add up the next few columns.
=IF(CONTAINS("No", Status@row, 100, IF([Four or more issues]@row = 1, 0, SUM([No Issue Score]@row:[Final Issue Scoring]@row))))

Hi @Joey135,
Your formula is mostly sound, you just have a bracket in the wrong place (bolded), as you need to close the CONTAINS:
=IF(CONTAINS("No", Status@row), 100, IF([Four or More issues]@row = 1, 0, SUM([No Issue Score]@row:[Final Issue Scoring]@row)))
This should fix your issue.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!