Max Collect Formula Error

B Young

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!

KPH

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

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

KPH

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

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

KPH

Glad I could help.