INDEX/MATCH/MAX Formula within a date range

Maggie Collins

Hello Smartsheet Community!

I am helping our team report on an individual who requests the most deliverables within a date range. Meaning, if Ted Williams requests the most deliverables in Q4, Ted Williams should show up in the report based on the countif/helper column.

Below is the formula I've been working with based on trial & error and not-so-helpful assistance from ChatGPT.

=INDEX({Primary Contact}, MATCH(MAX({Contact Count Quarter}), {Contact Count Quarter}, IF(AND({Submitted Date}, >=DATE(2024, 10, 1), {Submitted Date}, <=DATE(2024, 12, 31)))), 0)

Below is a screenshot with the sample data for reference:

Does anyone have any solutions or ideas on what I could be doing wrong with my formula? Thanks in advance for your assistance!

jmyzk_cloudsmart_jp

Hi @Maggie Collins

Here is a demo solution.

https://app.smartsheet.com/b/publish?EQBCT=e6fa890dd7d348978bb26dd1b14fdfa8

I added those helper columns;

• [Q] to determine the quarter.
• [SUM] to calculate the total [Contact Count Quarter] for each [Primary Contact]in the specified quarter.
• [Top in Q] to determine if the [SUM} of a row is the MAX in a quarter.

The Formulas are as follows;

[Q] =INT((MONTH([Submitted Date]@row) + 2.5) / 3)
[SUM] =SUMIFS([Contact Count Quarter]:[Contact Count Quarter], [Primary Contact]:[Primary Contact], [Primary Contact]@row, Q:Q, Q@row)
[Top in Q] =IF(SUM@row = MAX(COLLECT(SUM:SUM, Q:Q, Q@row)), 1)

Then, if you chose Q in the Sheet Summary field, the following formula shows the top [Primary Contact] in the Top field.

=INDEX([Primary Contact]:[Primary Contact], INDEX(COLLECT(Row:Row, Q:Q, Q#, [Top in Q]:[Top in Q], 1), 1))

Larry

If adding columns to the source sheet where Ted WIlliams & Lou Gehrig entries/rows are added, I would suggest a number of helper columns to make life easier. In the screenshot below I added 4 helper columns to do the hard work for us, & the final formula you need for the report helper or metric is very simple.

This Quarter: =IF(MONTH(TODAY()) > 9, "Q4", IF(MONTH(TODAY()) > 6, "Q3", IF(MONTH(TODAY()) > 3, "Q2", "Q1"))) + " " + YEAR(TODAY())

Quarter/Year: =IF(MONTH([Submitted Date]@row) > 9, "Q4", IF(MONTH([Submitted Date]@row) > 6, "Q3", IF(MONTH([Submitted Date]@row) > 3, "Q2", "Q1"))) + " " + YEAR([Submitted Date]@row)

Most Deliverables Count: =MAX(COLLECT([Contact Count Quarter]:[Contact Count Quarter], [Quarter/Year]:[Quarter/Year], [This Quarter]@row))

Most Deliverables: =IF([Contact Count Quarter]@row = [Most Deliverables Count]@row, 1, 0)

And the formula on the other sheet to return Ted Williams:

=INDEX(COLLECT({Primary Contact}, {Most Deliverables}, 1), 1)

This is certainly not the only way to do it but these helper columns will likely help facilitate reporting & are fully autonomous so long as the sheet is updated & saved on a regular basis.

GO SOX!

jmyzk_cloudsmart_jp

Considering Year as @Larry 's Quarter/Year makes the solution more useful!