Summation of Time Stamps

Excel file has a running time stamp (00:00:00 up to 23:59:59). Three columns where values for certain rows oscillate between 0 and 1023, nothing in between those two numbers, just a 0 or 1023. What I would like to do is sum up the time elapsed for when a certain column reads 1023. I've tried SUMIF, but that simply adds up the hrs, min, and sec. I need to add up the time elapsed between the a column changes to 1023 and when it changes back to 0. It happens rather often.

Looking for a formula where it will take the end of the time elapsed (i.e. when a value changes from 1023 to 0), subtract it from the beginning (i.e. when a value changes from 0 to 1023), and then repeat that for an entire column. Haven't had much luck so far.

See attached workbook.

Hi FMonyhan, welcome to Excel Forum! A small sample workbook (NOT a picture) would really help us to solve your problem quickly and accurately.
Please remove any personal or proprietary information.
Try to preserve the original layout so our solutions fit your workbook.
Provide “realistic” data. Include any variations the code or formula must address.
If appropriate, simulate some results to demonstrate what you want.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now click Browse, find your file, then click Upload. Simple!

Actually, "Col 1" (B) AND "Col 3"(D) contain "321". I assume you're suggesting using "Col 2"(C).

Try this:

Please Login or Register  to view this content.

It uses 3 clauses that generate TRUE/FALSE arrays for the entire range. The first determines if each element (starting with the second) is NOT equal to the previous element. The remaining two determine if a "run" is starting or ending. When combined mathematically, they yield a value of -1 (starting), 0 (ignored), or 1 (ending) for each cell in the check range. This value is then multiplied by the corresponding time, yielding an array of -starting and +ending times that can be SUMed. Finally, the total is adjusted for special cases : first cell = 1023 , last cell = 1023.

12:09:49	1023	-1
12:09:51	1023	0
12:09:51	1023	0
12:09:54	0	1
12:09:54	0	0

Just to be clear, the example run shown above would count as 5 seconds : 12:09:54 - 12:09:49