Duplicates with an extra step

Matt Travis

Hi,

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

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)