Index Collect with Dates

evanameyer1

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")))

Leibel S

@evanameyer1

Smartsheet does not allow one to nest a COLLECT within another COLLECT.

Leibel S

@evanameyer1

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"