I am trying to sum a set of columns if one condition does not exist.

I have tried a couple of formulas with no luck. If the first three criteria have "Fail" then the overall score should be Fail; however if not then I want to add the scores of the other columns together. Here's what I've tried so far. and I get #INCORRECT ARGUMENT for the if statement and #UNPASEABLE for the sum statement.

=SUMIFS([AF-S1]@row, "Fail", , [AF-S2]@row, "Fail", , [AF-S3]@row, "Fail", , [Total Call Opening Score]@row + [Total Communication Score]@row + [Total Ownership Score]@row + [Total Closure Score]@row = [Total Customer Centricity Score]@row)

=IF(OR([AF-S1]@row, [AF-S2]@row, [AF-S3]@row, "Fail", SUM([Total Call Opening Score]@row, [Total Communication Score]@row, [Total Ownership Score]@row, [Total Closure Score]@row, [Total Customer Centricity Score]@row)))

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!