Max Collect in Sheet Summary

Christina117

I'm trying to have the Sheet Summary list the employee name from the Repeat Category Winner column with the highest Repeat Count per Category, and I have 3 categories that I will use this for (Timesheet Not Submitted, Timesheet Not Approved and Time Off Not Approved). Formulas for those columns in the screenshots.

Humashankar

Hi @Christina117

To execute and find Maximum Repeat Count for Each Category - please use below formulae.

=MAXIFS([Repeat Count]:[Repeat Count], [Category]:[Category], "Timesheet Not Submitted")

=MAXIFS([Repeat Count]:[Repeat Count], [Category]:[Category], "Timesheet Not Approved")

=MAXIFS([Repeat Count]:[Repeat Count], [Category]:[Category], "Time Off Not Approved")

To execute and retrieve Employee Name with Maximum Repeat Count for Each Category - please use below code with those functions.

=INDEX([Employee Name]:[Employee Name], MATCH(MAXIFS([Repeat Count]:[Repeat Count], [Category]:[Category], "Timesheet Not Submitted"), [Repeat Count]:[Repeat Count], 0))

=INDEX([Employee Name]:[Employee Name], MATCH(MAXIFS([Repeat Count]:[Repeat Count], [Category]:[Category], "Timesheet Not Approved"), [Repeat Count]:[Repeat Count], 0))

=INDEX([Employee Name]:[Employee Name], MATCH(MAXIFS([Repeat Count]:[Repeat Count], [Category]:[Category], "Time Off Not Approved"), [Repeat Count]:[Repeat Count], 0))

At last to display Results in the Sheet Summary make use of the below

=INDEX([Max Repeat Count Timesheet Not Submitted]:[Max Repeat Count Timesheet Not Submitted], 1)

=INDEX([Max Repeat Count Timesheet Not Approved]:[Max Repeat Count Timesheet Not Approved], 1)

=INDEX([Max Repeat Count Time Off Not Approved]:[Max Repeat Count Time Off Not Approved], 1)

Hope this helps - Happy to help further!!

Thank you very much and have a great one!

Warm regards

Christina117

Hi Huma!

Thank you so much! Unfortunately, I'm receiving the #UNPARSEABLE error for all of them.

Christina

Paul Newcome

@Humashankar The MAXIFS function does not exist in Smartsheet. Our option for that is MAX/COLLECT.