Collect % (column 1) based on a checkbox (column 2) and region (column 3)

Zach C

What formula will collect a % (column 1) based on a checkbox (column 2 that indicates most recent month/day; column next to it has the specific months/days) and specific region - for example, "region 1" (column 3)

Paul Newcome

INDEX, but you wouldn't use the {Range} inside of the CONTAINS function. You would just use @cell exactly as I have it.

Zach C

I was thinking this would something along the lines of -

=COLLECT({%}, {Max_Date}, 1, {Location}, "Region 1")

I didn't think Smartsheet allows for COLLECT + IF formulas so open to suggestions.

Zach C

I've tried the below as well and getting "Incorrect Argument Set".

=INDEX(COLLECT({%}, {Max_Date}, 1, {Location}, "Region 1"), 1)

Zach C

raw data sheet for reference

Paul Newcome

What are you using to populate your checkbox?

Zach C

@Paul Newcome using =IFERROR(Date@row = MAX(Date:Date), 0)

Paul Newcome

How is the Location column populated? Do the cells have more than just "Region 1" in them?

Zach C

@Paul Newcome yeah there are different regional (and division) names

Zach C

@Paul Newcome the Date and Location columns involve manual intervention

Paul Newcome

Ok. So for the incorrect argument set issue, start by double and triple checking the ranges being referenced. Each should only be a single column.

After that, if there are cells with variations of "Region 1" such as "ABC - Region 1" and "DEF - Region 1", you are going to need to incorporate a CONTAINS function.

CONTAINS("Region 1", @cell)

Zach C

@Paul Newcome copy that! Would this need to an INDEX & COLLECT or just COLLECT?

• =COLLECT({%}, {Location}, CONTAINS("Region 1", {Location}), {Max_Date}, 1)

Or

• =INDEX(COLLECT({%}, {Location}, CONTAINS("Region 1", {Location}), {Max_Date}, 1), 1)

Paul Newcome

INDEX, but you wouldn't use the {Range} inside of the CONTAINS function. You would just use @cell exactly as I have it.

Zach C

@Paul Newcome Totally overlooked the @cell callout but made the update and it works. You're a Smartsheet Community hero - thanks Paul!!

Paul Newcome

Happy to help. 👍️