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

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    You might be overthinking the problem. Is it just the missing [ and ] around the column name? I've put them in bold here

    =MAX(COLLECT([No of Projects Closed]:[No of Projects Closed], [Rank for Completion]:[Rank for Completion], Rank@row))

  • B Young
    B Young ✭✭
    Answer ✓

    Thank you - I thought it must have been something simple!!!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!