Duplicates with an extra step


I have a requirement to track some data for a first time right statistic but i'm unsure how to get this to work within Smartsheets. If an engineer visits a customer machine then the serial number is logged. I need to track if the serial number comes up more than once in a month's worth of data. Then the extra step bit is recording a figure for each engineer. So say engineer A visits serial number 23545645LMK, then engineer B visits the same serial number, there should be a count of 1 against engineer A as they were the one that didn't fix it the first time. Then if engineer C goes back to the same serial number again, there would be a mark of 1 against engineer B too.

Any way I can use Smartsheets to get this to work?


  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭

    Hi @Matt Travis You would use a COUNTIFS formula to determine the different counts for each Engineer+Serial Combo.

    I've made some assumptions here that 1) you only want to look at the last 30 days worth of data. 2) That the serial numbers are checked on different dates

    =COUNTIFS(Date:Date, >TODAY(-30), Date:Date, >Date@row, Serial:Serial, =Serial@row, Engineer:Engineer, <>Engineer@row)

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!