Formula sum if for tot time calculation.

[asyamonique]

Good Day,
How can I get customers total time visit base on below table?
Many Thanks.

Customer ID	First Name	Last Name	Time In	Time Out		Customer ID	First Name	Last Name	Tot.Hrs.
12345	xxxxx	xxxxxxx	9:15	12:21		12345	xxxxx	xxxxxxx	?
12345	xxxxx	xxxxxxx	12:15	14:10		98765	xxxxx	xxxxxxx	?
12345	xxxxx	xxxxxxx	22:05	2:05
12345	xxxxx	xxxxxxx	2:15	4:22
98765	xxxxx	xxxxxxx	14:22	16:45
98765	xxxxx	xxxxxxx	15:30	18:22
98765	xxxxx	xxxxxxx	18:24	21:35
98765	xxxxx	xxxxxxx	19:35	23:00
98765	xxxxx	xxxxxxx	22:10	22:55

 

[DanteAmor]

I show you 2 options.

Option 1:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Customer ID	First Name	Last Name	Time In	Time Out		Customer ID	First Name	Last Name	Tot.Hrs.
2	12345	xxxxx	xxxxxxx	09:15	12:21		12345	xxxxx	xxxxxxx	11:08
3	12345	xxxxx	xxxxxxx	12:15	14:10		98765	xxxxx	xxxxxxx	12:36
4	12345	xxxxx	xxxxxxx	22:05	02:05
5	12345	xxxxx	xxxxxxx	02:15	04:22
6	98765	xxxxx	xxxxxxx	14:22	16:45
7	98765	xxxxx	xxxxxxx	15:30	18:22
8	98765	xxxxx	xxxxxxx	18:24	21:35
9	98765	xxxxx	xxxxxxx	19:35	23:00
10	98765	xxxxx	xxxxxxx	22:10	22:55
Sh1

Cell Formulas
Range	Formula
J2:J3	J2	=SUM(($A$2:$A$10=G2)*($B$2:$B$10=H2)*($C$2:$C$10=I2)*($E$2:$E$10)+ IF(($A$2:$A$10=G2)*($B$2:$B$10=H2)*($C$2:$C$10=I2)*($E$2:$E$10)< ($A$2:$A$10=G2)*($B$2:$B$10=H2)*($C$2:$C$10=I2)*($D$2:$D$10),24)- ($A$2:$A$10=G2)*($B$2:$B$10=H2)*($C$2:$C$10=I2)*($D$2:$D$10))
Press CTRL+SHIFT+ENTER to enter array formulas.

Option 2 with a helper column:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Customer ID	First Name	Last Name	Time In	Time Out	Aux	Customer ID	First Name	Last Name	Tot.Hrs.
2	12345	xxxxx	xxxxxxx	09:15	12:21	03:06	12345	xxxxx	xxxxxxx	11:08
3	12345	xxxxx	xxxxxxx	12:15	14:10	01:55	98765	xxxxx	xxxxxxx	12:36
4	12345	xxxxx	xxxxxxx	22:05	02:05	04:00
5	12345	xxxxx	xxxxxxx	02:15	04:22	02:07
6	98765	xxxxx	xxxxxxx	14:22	16:45	02:23
7	98765	xxxxx	xxxxxxx	15:30	18:22	02:52
8	98765	xxxxx	xxxxxxx	18:24	21:35	03:11
9	98765	xxxxx	xxxxxxx	19:35	23:00	03:25
10	98765	xxxxx	xxxxxxx	22:10	22:55	00:45
Sh2

Cell Formulas
Range	Formula
J2:J3	J2	=SUMIFS($F$2:$F$10,$A$2:$A$10,G2,$B$2:$B$10,H2,$C$2:$C$10,I2)
F2:F10	F2	=E2+IF(E2<D2,24)-D2

 

[asyamonique]

Thanks Dante,
I did some changes and used ur below formula.
Cheers.

=SUM(($A$2:$A$10=G2)*($E$2:$E$10)+
IF(($A$2:$A$10=G2)*($E$2:$E$10)<
($A$2:$A$10=G2)*($D$2:$D$10),24)-
($A$2:$A$10=G2)*($D$2:$D$10))

 

[Dave Patton]

T202309a.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Customer ID	First Name	Last Name	Time In	Time Out		Customer ID	First Name	Last Name	Tot.Hrs.
2	12345	xxxxx	xxxxxxx	09:15	12:21		12345	xxxxx	xxxxxxx	11:08
3	12345	xxxxx	xxxxxxx	12:15	14:10		98765	xxxxx	xxxxxxx	12:36
4	12345	xxxxx	xxxxxxx	22:05	02:05
5	12345	xxxxx	xxxxxxx	02:15	04:22
6	98765	xxxxx	xxxxxxx	14:22	16:45
7	98765	xxxxx	xxxxxxx	15:30	18:22
8	98765	xxxxx	xxxxxxx	18:24	21:35
9	98765	xxxxx	xxxxxxx	19:35	23:00
10	98765	xxxxx	xxxxxxx	22:10	22:55
2d

Cell Formulas
Range	Formula
J2:J3	J2	=SUMPRODUCT(--($A$2:$A$10=G2),$E$2:$E$10-$D$2:$D$10+($D$2:$D$10>$E$2:$E$10))

 

[asyamonique]

How can we add below text into the result as hr and min?

"[h]\h\r\s m\m\i\n"

 

[asyamonique]

Hello again,
I did add by using from my old datas. )
Fixed already.
Thanks

=IF(TEXT(SUMPRODUCT(--($B$3:$B$2000=J3),$E$3:$E$2000-$D$3:$D$2000+($D$3:$D$2000>$E$3:$E$2000)),"[h]\h\r\s m\m\i\n")="0hrs 0min","",TEXT(SUMPRODUCT(--($B$3:$B$2000=J3),$E$3:$E$2000-$D$3:$D$2000+($D$3:$D$2000>$E$3:$E$2000)),"[h]\h\r\s m\m\i\n"))

 

[Dave Patton]

Thanks for the feedback.
You could use custom formatting to show hrs and min