Need help with a formula for counting

Options
bborenstein
bborenstein ✭✭
edited 09/25/23 in Formulas and Functions

I have a sheet that is populated by a form. Form is completed at a front desk for when employees forget their ID badge.

I need a formula to count when an employee forgets their badge 3 or more times within a 3 month period. (Unique column to count would be their email column)

Answers

  • Darla Brown
    Darla Brown Overachievers
    Options

    Hi,

    Hope you are having a fantastic Monday!

    I typically use the Pivot App for this type of solution. As long as you have date and name (or email) columns, you can run the pivot to update automatically. Set the name as rows and date as columns then you can count the name.

    Does this work?

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Something along the lines of:

    =COUNTIFS([Employee contact]:[Employee contact], [Employee contact]@row, Date:Date, (@cell >= TODAY(-90)))

    You can do it to be months rather than days, but the formula would end up being a bit more complex due to year end/starts.

    Hope this helps, if you've any problems/questions then just post!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!