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.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!