If(OR and Value formula issues

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)

Nick Korna

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

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.

Nick Korna

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

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

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

@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

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.