Index question...I think?

[Compton]

Hey all, first post here. First off, please don't crucify me for using a google sheet AS AN EXAMPLE, my work's internet policies prevents me from getting the data to you guys in any other convenient format that I know of. Of course I use Excel, so please use excel functions or else I'll be really confused.

So, the file below shows the start and end times for faults on a machine. Once the machine has a fault, our system is notified immediately and our system will also tell us when the fault ends (columns A & B). I could easily sum up these times, but I am interested in finding out the total cumulative time we run on Machine 1 vs Machine 2. I could go through and do it by hand as I've done here, but my sheet is already decently large (this is just a sample).

So to reiterate, I'm trying to find the percentage of time we run Machine 1 vs running Machine 2. This can be decently accurately obtained by finding the time between the last end times on the machines.

Thanks!

https://docs.google.com/spreadsheets/d/1ZdQNowqBVPR-jd-lBR0OCitOBNNJAVpciRL78mpBnIg/edit?usp=sharing

<tbody>
</tbody>

 

[FDibbins]

Hi, welcome to the board

I am not able to access file-hosting sites (my problem, not yours), but based on what you said, if you already have the duration (if not, maybe use a helper to calc that), it sounds to me like you need to use the SUMIFS() functions. Something like...

=sumifs(duration-range, machine-number-range "machine 1")
do the same for the next (or all?) machines, and then you will have the values you need to calc the %

 

[Compton]

Thanks for the reply FDibbins. I've tried sumifs function but I ran into a problem, I might just be having a bad day though lol.

Here is mock up sample of my data:

Start Time End Time Duration Fault Description Alias
2017-3-19 18:37:37 2017-3-19 18:37:38 0:00:01 Not relevant Machine 2
*8 more faults on Machine 2 of varying times/duration. Faults are when the machine is not operating. These samples means machine stopped operating for 1 second*
2017-3-20 11:10:45 2017-3-20 11:10:47 0:00:03 Not relevant Machine 2
2017-3-20 13:33:43 2017-3-20 13:33:48 0:00:05 Not relevant Machine 1
*2 more faults on Machine 1 of varying times/duration*
2017-3-20 14:27:11 2017-3-20 14:27:12 0:00:01 Not relevant Machine 1
2017-3-20 16:08:59 2017-3-20 16:09:01 0:00:02 Not relevant Machine 2
*4 more faults on Machine 2 of varying times/duration*
2017-3-21 03:22:44 2017-3-21 03:22:54 0:00:10 Not relevant Machine 2

So I am given the datetime of when the machine is NOT operating. I would like to approximate the cumulative time that the each machine IS operating. Not all faults are seconds, some can be hours given certain circumstances and only some faults require me to switch from one machine to the other. The duration that the machines are running are, for the sake of the conversation, random. Might run for 50 seconds then have a bad thing happen so it switches to the other machine. That next machine might run for days on end until we need to switch back to the other one.

To put it in excel terms, I would like to do something similar to =sumif(first two columns, Alias column, "Machine 1") and same for Machine 2 but that just gives the sum of the duration when I am actually looking for something like =sum(1st italic underline - first underline, 4th italic underline - 3rd italic underline) which would give Machine 2's approximate time online while =sum(3rd italic underline - 2nd italic underline) would give Machine 1.

There are only two machines, the main one and its backup for whenever the main one fails.

Thanks again!

 

[Compton]

Sorry, I had my fake excel sheet spaced out all nice and pretty but thanks to trolls I guess forums don't allow "unnecessary" spacing.