Using sumif between two columns with an additional criteria

[Luisdp]

I have a table with 4 Columns.

ID Name Start_Time Finish_Time
VSL1 Name1 15:00 18:00
VSL1 Name1 12:00 16:00
VSL2 Name2 05:00 15:00
VSL2 Name2 12:00 15:00

What I want to do is to get the total worked time of every ID.

For example, for VSL1 would be 18:00 - 12:00, and for VSL2 15:00 - 05:00.

I am struggling with this formula! Please HELP!!

 

[iggydarsa]

Is creating another column an option? if so, I would create a fifth column with the following formula:

=D2-C2

and use the following formula to get the total:

=SUMIF(A:A, K1,E:E )

K1 is being the cell that has an ID to look for, i.e.: VSL1

 

[tybaltlives]

wouldnt VSL1 be 18-15 + 16-12 = 3 + 4 = 7? In your example, you appear to be subtracting the minimum start time time for an ID from the maximum finish time for the ID. This also is possible but I just wanted to be sure what you wanted. Also, can the finish time extend beyond Midnight into the next day? Or can the start time begin before midnight?

-R

 

[Luisdp]

Creating another Column is not an option :/ Thats why I'm struggling so much with the solution. I am looking for an array formula maybe that could help me.

 

[Luisdp]

Exactly, what I wan to do is take the minimum time of start and maximun of finish. In that way I will have the total worked time Finish - Start. And answering to your question, the finish time and the start time can past midnight, in other words it can be between several days.

wouldnt VSL1 be 18-15 + 16-12 = 3 + 4 = 7? In your example, you appear to be subtracting the minimum start time time for an ID from the maximum finish time for the ID. This also is possible but I just wanted to be sure what you wanted. Also, can the finish time extend beyond Midnight into the next day? Or can the start time begin before midnight?

-R

 

[RabbitHole]

This formula is based on "ID" is in column A, "start time" in column C and "finish time" in column D

=MAX(IF(A:A=A1,D:D))-MIN(IF(A:A=A1,C:C)

A1 is where the ID you want to find the details and then press ctrl+shift+enter . Ensure the destination cell is also formatted as time.

 

[Luisdp]

That works perfect BUT what I want to get its the total time of all IDs. :/

HELP!

This formula is based on "ID" is in column A, "start time" in column C and "finish time" in column D

=MAX(IF(A:A=A1,D:D))-MIN(IF(A:A=A1,C:C)

A1 is where the ID you want to find the details and then press ctrl+shift+enter . Ensure the destination cell is also formatted as time.

 

[tybaltlives]

I had a similar idea. You need to enter your times as shown in this example in the event that times cross over midnight.

Excel 2010
	A	B	C	D
1	ID	Name	Start_time	Finish_time
2	VSL1	Name1	1/15/15 3:00 PM	1/15/15 6:00 PM
3	VSL1	Name2	1/15/15 12:00 PM	1/15/15 4:00 PM
4	VSL2	Name2	1/15/15 5:00 AM	1/15/15 3:00 PM
5	VSL2	Name2	1/15/15 12:00 PM	1/15/15 3:00 PM
6	VSL3	Name3	1/14/15 10:00 PM	1/15/15 6:00 PM
7	VSL3	Name3	1/15/15 12:00 PM	1/15/15 4:00 PM
8
9
10
11		VSL1	6.00
12		VSL2	10.00
13		VSL3	20.00
Sheet4

Cell Formulas
Range	Formula
C11	{=(MAX(IF($A$2:$A$7=B11,$D$2:$D$7))-MIN(IF($A$2:$A$7=B11,$C$2:$C$7)))*24}
C12	{=(MAX(IF($A$2:$A$7=B12,$D$2:$D$7))-MIN(IF($A$2:$A$7=B12,$C$2:$C$7)))*24}
C13	{=(MAX(IF($A$2:$A$7=B13,$D$2:$D$7))-MIN(IF($A$2:$A$7=B13,$C$2:$C$7)))*24}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RabbitHole]

That works perfect BUT what I want to get its the total time of all IDs. :/

HELP!

do you a list of individual IDs or does it have to be pulled out from the data group. if you already have a list, it is easy otherwise it can ce tricky

 

[tybaltlives]

ya, agreed - thats why I made a separate list of the IDs