CODEWITHSUNDEEP
excelif-statementexcel-formulalogical-operators
Uhujkill
Uhujkill

Reputation: 89

Formula to calculate difference in time for specific criteria

I am attempting to calculate the downtime of our washers. I have an Excel sheet which displays all our cycle information for our washer machines.

Column B is the start time of the Machine.
Column E is the time difference between one cycle and another.
Column J displays either "None" or "General Washer Error".

What I am attempting is to calculate the time between the last successful Washer Cycle (Where J="None") and the next successful Wash Cycle, but only where there is a failure between them (Where J="General Washer error").

I know I can total the E Column, but this will give me an inaccurate total of downtime as it adds all successful Cycles also.

I have attached an image of the sheet for help.

Excel Sheet Grab

Upvotes: 0

Views: 582

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

I think this formula is what you want:

=SUMIFS(E:E,D:D,"<>none")

This assumes that System Error Text is actually in column D instead of column J. The image seems to show this, contrary to the description.

Edit 1

Ok. Based on your actual worksheet you sent me:

1.) The data in column E are entered into the worksheet as text. They are not numbers. So the SUMIFS() function that I used in my 1st formula will never work for text values. For example, my formula produces a total of zero (not an error) when I open your spreadsheet here with your text values.

2.) Since the formula did not error on my machne I suspect that you are using an older version of Excel that does not include the SUMIFS() function and that is why you get an error instead of a zero.

So, you need a different formula. Here is one that will work in older versions of Excel and it does not care that the values stored in column E are actually text instead of true numbers:

=SUMPRODUCT((D3:D2002<>"none")*(E3:E2002))

Upvotes: 1

Related Questions