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
Check out the Formula Handbook template!