Max Collect in Sheet Summary

Options

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.


Answers

  • Humashankar
    Humashankar ✭✭✭✭✭
    Options

    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

    Huma

    Community Leader

  • Christina117
    Options

    Hi Huma!

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

    Christina

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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