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

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭✭
    edited 10/31/24

    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, "")))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you have Description@row in your formula, you have to have a column called Description for the formula to work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!