Index Collect with Dates
Hi,
I'm looking to get a bunch of different outputs based on:
1) an account being the smallest/largest (min(contract spend))
2) an account being in a given month ({Assigned Date}, IFERROR(MONTH(@cell), 0) = MONTH([Month (just update top row)]@row))
3) various other variables such as customer segment, etc
The thing is, I have already created an exact version of this sheet, just using a given contact as the main variable as opposed to date. I've copied variables over to this new sheet, and every single formula worked except the ones that involved : smallest/largest account + shortest/longest account duration. I set up the formula the exact same way, just changing that one thing and every time I get a #nesting criteria error, even though I'm not using any SUMIFS or COUNTIFS statements.
This is what is looks like:
=IFERROR(INDEX(COLLECT({Account Name}, {Contract Spend}, MIN(COLLECT({Contract Spend}, {Assigned Date}, IFERROR(MONTH(@cell), 0) = MONTH([Month (just update top row)]@row), {Assigned Date}, IFERROR(MONTH(@cell), 0) = MONTH([Month (just update top row)]@row)), 1), "N/A")))
Answers
-
Smartsheet does not allow one to nest a COLLECT within another COLLECT.
-
A way around this would be to make a helper column in your source sheet that calculates if this is the smallest/largest contract spend.
You can then add that column to your criteria, something like this: {smallest / Largest}, "Smallest"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!