Sort Formula issues
Hi,
This formula causes a #unparseable and can't figure out what is wrong:
=IF(Description@row = "Overall Project Risk", "0-00", IF([RAID Type]@row = "", "", IF(Status@row = "Complete", 7, IF([RAID Type]@row = "Decision", 1, IF([RAID Type]@row = "Issue", 2, IF([RAID Type]@row = "Risk", 3, IF([RAID Type]@row = "Action", 4, IF([RAID Type]@row = "Complete", 6, ""))))))) + "-" + IF(LEN(COUNTIFS([Finish]:[Finish], <[Finish]@row, [RAID Type]:[RAID Type], [RAID Type]@row)) = 1, "0" + COUNTIFS([Finish]:[Finish], <[Finish]@row, [RAID Type]:[RAID Type], [RAID Type]@row), COUNTIFS([Finish]:[Finish], <[Finish]@row, [RAID Type]:[RAID Type], [RAID Type]@row))))
Here are the column headers and I am plugging this formula into the first cell beneath "Sort." The intention is to carry the formula down the columm (so plan is to convert it to a column formula). Appreciate any insight!
Thank you!
Answers
-
Try breaking it up into multiple columns.
So you would have a "Sort Helper 1" column that would contain your first IF statements
=IF(Description@row = "Overall Project Risk", "0-00", IF([RAID Type]@row = "", "", IF(Status@row = "Complete", 7, IF([RAID Type]@row = "Decision", 1, IF([RAID Type]@row = "Issue", 2, IF([RAID Type]@row = "Risk", 3, IF([RAID Type]@row = "Action", 4, IF([RAID Type]@row = "Complete", 6, "")))))))
Then "Sort Helper 2" would have your second set of IF statements
IF(LEN(COUNTIFS([Finish]:[Finish], <[Finish]@row, [RAID Type]:[RAID Type], [RAID Type]@row)) = 1, "0" + COUNTIFS([Finish]:[Finish], <[Finish]@row, [RAID Type]:[RAID Type], [RAID Type]@row), COUNTIFS([Finish]:[Finish], <[Finish]@row, [RAID Type]:[RAID Type], [RAID Type]@row))))
Then your SORT column would be a simple = JOIN([Sort Helper 1]@row:[Sort Helper 2],-)
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
-
Thanks - looks like there is still an issue with the first formula for Sort 1 column, but the formula works in Sort 2! Do you see what may be wrong with it?
Sort 1 formula
=IF(Description@row = "Overall Project Risk", "0-00", IF([RAID Type]@row = "", "", IF(Status@row = "Complete", 7, IF([RAID Type]@row = "Decision", 1, IF([RAID Type]@row = "Issue", 2, IF([RAID Type]@row = "Risk", 3, IF([RAID Type]@row = "Action", 4, IF([RAID Type]@row = "Complete", 6, "")))))))
-
It looks like you may have some parenthesis out of place. You should have 8 after the first set of nested IFs, but you only have 7. You should also only have 2 at the end, but you have 4.
-
Fixed the parenthesis but still get #unparseable
-
Do you have a column called "Description" in your sheet? It looks like maybe it is actually called [Task Name] now?
-
Its the description in the row not the column. I had copied and pasted this formula over from my previous company and have all the columns the same except RAIDD Type is now RAID Type. It didn't need a Column named "Description" for it to work.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 214 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 457 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!