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
-
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))
-
Thank you - I thought it must have been something simple!!!
Answers
-
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))
-
Thank you - I thought it must have been something simple!!!
-
Glad I could help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!