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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!