Sort Formula issues

WhitneyOliver

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!

Michelle Choate 2

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

WhitneyOliver

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

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.

WhitneyOliver

Fixed the parenthesis but still get #unparseable

Paul Newcome

Do you have a column called "Description" in your sheet? It looks like maybe it is actually called [Task Name] now?

WhitneyOliver

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

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