Excel Help - SUMIF without Duplicates



Hi guys, long time lurker first time poster. I'm looking for some Excel help with a formula I'm trying to write. The attached file has some sample data for a process line. Each row represents a "ping" to request data from the system. Each is time stamped and we are tracking line speed in column B, any fault values in column C, the fault status in column D, and I've added a formula in column E to show when the line is running over 200 feet/min. This is what I'm trying to calculate, the total time the line is running over 200 feet/min in a given 8 hour turn.

The problem is, the frequency of the "pings" sometimes provides two rows of data with the same time stamp. This is throwing off my caluclations of total time spent running over 200 feet/min. I've tried to come up with a formula in H2, but can't figure out how to tell it to continually reference the cell below it and not include any where those two cells are the same in the sum calculation. Could any of the Excel guru's take a look and provide any insight? :thanks:


  • Mar12_Turn3.xlsx
    258.8 KB · Views: 131

Steve Prevette

Deming Disciple
Super Moderator
Try This. Basically a two step process - calculate if the current row is a duplicate of the previous row, store that answer, and the array formulae "sum if" for that result.


  • Mar12_Turn3 SSP.xlsx
    259.1 KB · Views: 200


Thanks Steve, much appreciated. I think that's going to give me what I'm looking for.


Steve pretty much nailed it with his spreadsheet. Short of manually deleting the duplicates, there's not really a better way of doing what you want.

But I can simplify the sheet very slightly. No SUMIF function. Just add the number ones together.


  • Mar_12Turn3 - edited.xls
    10 KB · Views: 162
Top Bottom