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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!