If(OR and Value formula issues

Options
Joey135
Joey135 ✭✭✭✭

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)

Tags:

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    Nick Korna ✭✭✭✭✭✭
    edited 04/12/24 Answer βœ“
    Options

    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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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!

  • Joey135
    Joey135 ✭✭✭✭
    Options

    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)))

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    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? πŸ™‚

  • Joey135
    Joey135 ✭✭✭✭
    Options

    @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))))

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 04/12/24 Answer βœ“
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!