Max Collect Formula Error

We have a column 'No of Projects Closed' which has a column formula in it; =IFERROR(COUNTIFS({Completion Date}, >=[Start Date]@row, {Completion Date},<=[End Date]@row),"")

{Completion Date} references another sheet

Then we have another column which ranks the column 'No of Projects Closed' called 'Rank for Completion' The formula in this column is =IFERROR(RANKEQ([No of Projects Closed]@row, [No of Projects Closed]$1:[No of Projects Closed]$49), "")

Both of these columns work perfectly.

I then have a separate section which has a column 'Rank' and listed in this column is the following; 1, 2, 3, 4, 5 so we can quickly see top 5 no of projects closed. The column next to the 'Rank' is where I was hoping it would pull in the data from the column 'No of projects closed' for each of the ranks. I have tried this formula: =MAX(COLLECT([No of Projects Closed]:[No of Projects Closed], Rank for Completion: Rank for Completion, [Rank]@row)) but it brings up #UNPARSEABLE

I already have used this formula on another sheet and it works fine, so I'm trying to figure out what could be wrong in my columns or forumla?

Thank you for your help!

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!