# Excel Help - SUMIF without Duplicates

J

#### jabster410

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?

#### Attachments

• 258.8 KB Views: 123

#### Steve Prevette

##### Deming Disciple
Staff member
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.

#### Attachments

• 259.1 KB Views: 192
J

#### jabster410

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

N

#### NumberCruncher

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.

#### Attachments

• 10 KB Views: 152
Help me build an Excel Spreadsheet for tracking training Training - Internal, External, Online and Distance Learning 14
Zeiss CMM Calypso output to Excel or CSV file Help Inspection, Prints (Drawings), Testing, Sampling and Related Topics 17
S Excel Help - Equivalent of "Range(\$G\$14:\$AD\$121).Select" in R1C1 notation Excel .xls Spreadsheet Templates and Tools 5
Unusual problem with Excel - Need some help Excel .xls Spreadsheet Templates and Tools 7
Excel 2003 and Capability Studies for Dummies - Help needed! Excel .xls Spreadsheet Templates and Tools 4
C Corrupt Excel File - Help!!!!!!! Excel .xls Spreadsheet Templates and Tools 9
A MS Excel Font Help from ASQ, AIAG and/or Others Excel .xls Spreadsheet Templates and Tools 6
Excel - Sorting by Product or Raw Materials [need Help] Excel .xls Spreadsheet Templates and Tools 4
S Distribution Data Graphing in Excel help Excel .xls Spreadsheet Templates and Tools 4
S Excel Spreadsheet Conditional Formatting help needed Excel .xls Spreadsheet Templates and Tools 12
T Excel Time Calculations and Formulas help needed Excel .xls Spreadsheet Templates and Tools 15
Q Excel worksheet - Help required (viewing specific number of rows/columns) Excel .xls Spreadsheet Templates and Tools 4
S How to make graphs with the help of Excel--Like Gaussian distribution, control chart Capability, Accuracy and Stability - Processes, Machines, etc. 1
Y ISO: Help With Records Numbering System & Log in Excel or Access Document Control Systems, Procedures, Forms and Templates 12
Excel Cell or Text Formatting help Excel .xls Spreadsheet Templates and Tools 27
R Need Help with MS-Excel for Mac Excel .xls Spreadsheet Templates and Tools 5
D Need some help with a formula in Excel Please! Excel .xls Spreadsheet Templates and Tools 11
Samples Log sheet - need help (Excel Functions) Excel .xls Spreadsheet Templates and Tools 1
D How do I Auto fill colour on excel if certain risk criteria is met on PFMEA? Help! Excel .xls Spreadsheet Templates and Tools 13
A Need help make a bin in normdist - Normal Distribution - Excel Excel .xls Spreadsheet Templates and Tools 4
M Filtering Help with Excel Excel .xls Spreadsheet Templates and Tools 4
The IF function in Excel - Need help Excel .xls Spreadsheet Templates and Tools 14
S Need help to test my excel worksheet based on ISO 3951 Excel .xls Spreadsheet Templates and Tools 1
Excel Formula for Ebay shop fees - Help needed Excel .xls Spreadsheet Templates and Tools 8
W Excel Function SUMPRODUCT - Need help! Excel .xls Spreadsheet Templates and Tools 11
M Need excel formating help - Have excel automatically populate a column Excel .xls Spreadsheet Templates and Tools 19
J Seeking Excel Spreadsheet to help with cycle test for Weibull failure rates Excel .xls Spreadsheet Templates and Tools 2
M Can someone help me create a POISSON ACUMULATIVE PROBABILITY table in Excel? Excel .xls Spreadsheet Templates and Tools 1
Incoming Inspection Records using Excel File ISO 13485:2016 - Medical Device Quality Management Systems 2
95% UCL - Attaching an excel for recovery study Statistical Analysis Tools, Techniques and SPC 0
EXCEL - How do I make the cell stay blank until a date is entered in N7? Excel .xls Spreadsheet Templates and Tools 6
Validating Excel (XLS) formulas 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 7
Template for Excel Validation Reliability Analysis - Predictions, Testing and Standards 6
Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
Issuing of CoC to Customer in a Word or Excel format ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Track audit findings on Excel tracker Excel .xls Spreadsheet Templates and Tools 9
Does anyone have an excel IATF 16949 Internal Audit checklist I could use? IATF 16949 - Automotive Quality Systems Standard 7
Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
Commercial excel templates that calculate overhead, hourly rates, fee proposal Excel .xls Spreadsheet Templates and Tools 0
Cannot Export multiple sections from Excel to PDF without losing pagination Excel .xls Spreadsheet Templates and Tools 23
Matching two matrices in Excel Excel .xls Spreadsheet Templates and Tools 5
Level 3 KPI Excel Template Manufacturing and Related Processes 1
Informational New AIAG PFMEA Process - Excel Template Attached FMEA and Control Plans 23
Excel PPAP Tracking templates or tool wanted APQP and PPAP 1
Part 11 Compliance, Excel living documents (i.e. document master list, equipment list, approved supplier list) Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 3
ISO 9001:2015 Excel Gap Analysis wanted ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
AS9110 Audit Check Sheet wanted Excel or Word Document AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
G Excel report with pictures Excel .xls Spreadsheet Templates and Tools 5
Query regarding MS Excel usage Excel .xls Spreadsheet Templates and Tools 2
Excel OOT (Out of Tolerance) Formula Excel .xls Spreadsheet Templates and Tools 3