Formula with multiple IF(AND statements
I am having trouble with a formula that will return a 1, 3, 5, or 10 based on a combination of two columns.
Anything selected as REPORTED will equal 1. Easy enough. But in the same column, If FIXED Other points will be rewarded based on the Category it is paired with. Here is what I have so far:
=IF([Reported or Fixed?]1 = "Reported", 1, IF(AND([Reported or Fixed?]1 = "fixed", Category1 = "Find & Fix"), 5, IF(AND([Reported or Fixed?]1 = "fixed", Category1 = "Procedure Review", 3, IF(AND([Reported or Fixed?]1 = "Fixed", Category1 = "Hands Free / Ergonomics Find & Fix", 10))))))
It works for the first part of the formula, but as I added to it, it now returns INCORRECT ARGUMENT SET.
Answers

Are the only two options for the column "REPORTED" or "FIXED"? If so, you can use some built in logic to simplify. The nested IF statement will work from left to right and stop on the first true value. So if the first argument is if it equals "REPORTED" then output 1, everything after that (if the only other option is "FIXED") will be assumed to be "FIXED" which means that you don't have to specify that part.
=IF([Reported or Fixed?]1 = "Reported", 1, IF(Category1 = "Find & Fix", 5, IF(Category1 = "Procedure Review", 3, IF(Category1 = "Hands Free / Ergonomics Find & Fix", 10))))

Thank you very much!😎

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!