Lookup with criteria and give actual sum base on two criteria

[Lukma]

Hi Guys

well am trouble today as my boss ask me to update my data in a way to sum the actual of time spent for each each location as per voyage in other column

And i have created my data in this way that in Column L4 and M4 which i have giving a sample

So i need a solution that extract unique Rig Name in Column L4 and to give me the actual spent hours in column M4 , i will appreciate if i can get a solution to this trouble

Regards

If there is a formula to ease my stress i will be more glade with solution

Book2
	B	C	D	E	F	G	H	I	J	K	L	M
2			Criteria 2				Criteria 1
3	Month	Issued Month	Voyage	Vessel Name	Deck Status	Departed	Rig Name	Start Time	End Time	Hours	Rig Name	Actual Hours
4	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	02/01/21 08:55	02/01/21 12:15	3:20	USSC	7:25
5	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	Jopetwill-300	02/01/21 13:35	02/01/21 19:50	6:15	Jopetwill-300	6:15
6	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	02/01/21 20:25	02/01/21 21:30	1:05
7	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	03/01/21 07:45	03/01/21 10:45	3:00
8	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Al Ghallan Island	02/01/21 07:10	02/01/21 12:18	5:08	Al Ghallan Island	5:08
9	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Asseifiya Island	02/01/21 14:18	02/01/21 15:00	0:42	Asseifiya Island	0:42
10	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Ettouk Island	02/01/21 17:40	02/01/21 18:42	1:02	Ettouk Island	1:02
11	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Umm Al Anbar	03/01/21 07:00	03/01/21 10:42	3:42	Umm Al Anbar	3:42
12	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Deep Driller-3	04/01/21 04:18	04/01/21 16:42	12:24	Deep Driller-3	12:24
13	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Artabhatt-1	29/12/20 08:15	29/12/20 15:20	7:05	Rig Artabhatt-1	21:35
14	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Al Bzoom	30/12/20 15:50	30/12/20 21:00	5:10	Rig Al Bzoom
15	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Artabhatt-1	31/12/20 04:00	31/12/20 18:30	14:30
16	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Rig Artabhatt-1	02/01/21 09:45	02/01/21 10:55	1:10
17	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Barge Pride	02/01/21 13:40	02/01/21 16:30	2:50
18	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Rig Diyina	02/01/21 20:45	03/01/21 02:40	5:55
19	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Deep Driller-3	03/01/21 10:15	03/01/21 12:10	1:55
20	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Deep Driller-3	03/01/21 20:10	04/01/21 04:00	7:50
21	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Rig Hudairiyat	02/01/21 16:18	04/01/21 16:30	48:12
22	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Rig Hudairiyat	04/01/21 21:00	05/01/21 11:54	14:54
23	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Barge Shamal	15/01/21 21:00	16/01/21 01:06	4:06
24	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Lulu	01/01/21 23:59	02/01/21 00:45	0:46
25	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Hail	02/01/21 11:25	02/01/21 13:00	1:35
26	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig VKN-3	02/01/21 22:00	02/01/21 23:10	1:10
27	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Ghallan	03/01/21 08:35	04/01/21 03:45	19:10
28	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig SMS Faith	03/01/21 06:00	03/01/21 08:48	2:48
29	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Al Gharbia	03/01/21 12:00	03/01/21 21:00	9:00
30	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Marawwah	04/01/21 00:35	04/01/21 14:50	14:15
31	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Yemillah	04/01/21 16:30	05/01/21 00:01	7:31
32	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Al Hail	05/01/21 05:30	05/01/21 20:00	14:30
33	01-Jan-21	Jan-21	3711	MAC PHOENIX	Half	01/01/21 21:22	Rig Muhaiyimat	02/01/21 04:13	02/01/21 15:45	11:32
34	01-Jan-21	Jan-21	3711	MAC PHOENIX	Half	01/01/21 21:22	Deep Driller-3	02/01/21 17:30	03/01/21 12:15	18:45
35	01-Jan-21	Jan-21	3712	ADNOC-812	Half	01/01/21 21:15	Barge Al Hyleh	02/01/21 09:45	02/01/21 18:10	8:25
36	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge MB-1	02/01/21 10:45	02/01/21 15:50	5:05
37	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge Pesto	03/01/21 08:30	03/01/21 13:25	4:55
38	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZWSC	04/01/21 08:40	04/01/21 09:20	0:40
39	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZCSC	04/01/21 13:40	04/01/21 17:45	4:05
40	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZCSC	04/01/21 15:53	04/01/21 17:45	1:52
41	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge MB-1	04/01/21 20:00	04/01/21 20:30	0:30
42	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZWSC	05/01/21 08:40	05/01/21 11:45	3:05
43	01-Jan-21	Jan-21	1	LCT-TARFFAH	Full	03/01/21 07:10	Zirku Island	03/01/21 20:40	04/01/21 08:50	12:10
44	01-Jan-21	Jan-21	1	LCT-TARFFAH	Full	03/01/21 07:10	Ettouk Island	04/01/21 17:40	05/01/21 08:25	14:45
45	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	03/01/21 23:15	04/01/21 01:25	2:10
46	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	UZWSP	04/01/21 02:50	04/01/21 03:45	0:55
47	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	04/01/21 08:30	04/01/21 18:30	10:00
48	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	05/01/21 07:25	05/01/21 11:50	4:25
49	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	UZNSP	05/01/21 13:00	05/01/21 15:30	2:30
50	01-Jan-21	Jan-21	3	ADNOC-510	Full	02/01/21 14:20	Rig Sms Mariam	03/01/21 07:20	04/01/21 02:15	18:55
51	01-Jan-21	Jan-21	4	A-GRACE	Full	02/01/21 06:15	Rig Makasib	03/01/21 05:03	05/01/21 12:08	55:05
52	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	03/01/21 07:30	03/01/21 14:20	6:50
53	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	04/01/21 07:40	04/01/21 17:30	9:50
54	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	Barge Pesto	04/01/21 18:08	04/01/21 18:55	0:47
55	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	05/01/21 07:55	05/01/21 18:25	10:30
56	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Al Ghallan	03/01/21 10:50	03/01/21 11:25	0:35
57	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Yemillah	03/01/21 12:30	03/01/21 14:00	1:30
58	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Al Hail	04/01/21 01:10	04/01/21 17:30	16:20
59	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Makasib	05/01/21 19:30	06/01/21 07:40	12:10
60	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Qatia Island	03/01/21 19:20	03/01/21 22:00	2:40
61	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Bu Sikeen Island	04/01/21 07:10	04/01/21 10:00	2:50
62	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Qatia Island	04/01/21 13:40	04/01/21 22:30	8:50
63	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Ghallan Island	05/01/21 08:45	05/01/21 12:00	3:15
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:F63	Cell Value	contains "NAF"	text	NO

 

[etaf]

A pivot table may do what you need, and work instantly calculating the results you need

i can see how the Rig and time works not sure how the Voyage part meant to work exactly , as you are not showing that in the example

any way hers a pivot table

chemistry_v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Criteria 2				Criteria 1
2	Month	Issued Month	Voyage	Vessel Name	Deck Status	Departed	Rig Name	Start Time	End Time	Hours	Rig Name	Actual Hours		Rig Name	Voyage	Sum of Hours
3	44197	44197.03472	3705	ADNOC-810	Half	1/1/21 9:30	USSC	1/2/21 8:55	1/2/21 12:15	03:20:00	USSC	07:25:00		ACPT	2	16:35:00
4	44197	44197.03472	3705	ADNOC-810	Half	1/1/21 9:30	Jopetwill-300	1/2/21 13:35	1/2/21 19:50	06:15:00	Jopetwill-300	06:15:00		Al Ghallan Island	7	03:15:00
5	44197	44197.03472	3705	ADNOC-810	Half	1/1/21 9:30	USSC	1/2/21 20:25	1/2/21 21:30	01:05:00					3706	05:08:00
6	44197	44197.03472	3705	ADNOC-810	Half	1/1/21 9:30	USSC	1/3/21 7:45	1/3/21 10:45	03:00:00				Al Qatia Island	7	11:30:00
7	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Al Ghallan Island	1/2/21 7:10	1/2/21 12:18	05:08:00	Al Ghallan Island	05:08:00		Asseifiya Island	3706	00:42:00
8	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Asseifiya Island	1/2/21 14:18	1/2/21 15:00	00:42:00	Asseifiya Island	00:42:00		Barge Al Hyleh	3712	08:25:00
9	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Ettouk Island	1/2/21 17:40	1/2/21 18:42	01:02:00	Ettouk Island	01:02:00		Barge MB-1	3713	05:35:00
10	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Umm Al Anbar	1/3/21 7:00	1/3/21 10:42	03:42:00	Umm Al Anbar	03:42:00		Barge Pesto	5	00:47:00
11	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Deep Driller-3	1/4/21 4:18	1/4/21 16:42	12:24:00	Deep Driller-3	12:24:00			3713	04:55:00
12	44166	44197.47917	3666-O	ASL SWIFT	Half	1/2/21 16:00	Rig Artabhatt-1	12/29/20 8:15	12/29/20 15:20	07:05:00	Rig Artabhatt-1	21:35:00		Barge Pride	3707	02:50:00
13	44166	44197.47917	3666-O	ASL SWIFT	Half	1/2/21 16:00	Rig Al Bzoom	12/30/20 15:50	12/30/20 21:00	05:10:00	Rig Al Bzoom			Barge Shamal	3708	04:06:00
14	44166	44197.47917	3666-O	ASL SWIFT	Half	1/2/21 16:00	Rig Artabhatt-1	12/31/20 4:00	12/31/20 18:30	14:30:00				Bu Sikeen Island	7	02:50:00
15	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Rig Artabhatt-1	1/2/21 9:45	1/2/21 10:55	01:10:00				Deep Driller-3	3706	12:24:00
16	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Barge Pride	1/2/21 13:40	1/2/21 16:30	02:50:00					3707	09:45:00
17	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Rig Diyina	1/2/21 20:45	1/3/21 2:40	05:55:00					3711	18:45:00
18	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Deep Driller-3	1/3/21 10:15	1/3/21 12:10	01:55:00				Ettouk Island	1	14:45:00
19	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Deep Driller-3	1/3/21 20:10	1/4/21 4:00	07:50:00					3706	01:02:00
20	44197	44197.35417	3708	SMIT LUZON	Full	1/1/21 20:30	Rig Hudairiyat	1/2/21 16:18	1/4/21 16:30	00:12:00				Jopetwill-300	3705	06:15:00
21	44197	44197.35417	3708	SMIT LUZON	Full	1/1/21 20:30	Rig Hudairiyat	1/4/21 21:00	1/5/21 11:54	14:54:00				Rig Al Bzoom	3666-O	05:10:00
22	44197	44197.35417	3708	SMIT LUZON	Full	1/1/21 20:30	Barge Shamal	1/15/21 21:00	1/16/21 1:06	04:06:00				Rig Al Ghallan	6	00:35:00
23	44197	44197.22222	3709	SMIT LUMUT	Half	1/1/21 19:15	Rig Al Lulu	1/1/21 23:59	1/2/21 0:45	00:46:00					3709	19:10:00
24	44197	44197.22222	3709	SMIT LUMUT	Half	1/1/21 19:15	Rig Al Hail	1/2/21 11:25	1/2/21 13:00	01:35:00				Rig Al Gharbia	3710	09:00:00
25	44197	44197.22222	3709	SMIT LUMUT	Half	1/1/21 19:15	Rig VKN-3	1/2/21 22:00	1/2/21 23:10	01:10:00				Rig Al Hail	6	16:20:00
26	44197	44197.22222	3709	SMIT LUMUT	Half	1/1/21 19:15	Rig Al Ghallan	1/3/21 8:35	1/4/21 3:45	19:10:00					3709	01:35:00
27	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig SMS Faith	1/3/21 6:00	1/3/21 8:48	02:48:00					3710	14:30:00
28	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig Al Gharbia	1/3/21 12:00	1/3/21 21:00	09:00:00				Rig Al Lulu	3709	00:46:00
29	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig Marawwah	1/4/21 0:35	1/4/21 14:50	14:15:00				Rig Artabhatt-1	3707	01:10:00
30	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig Yemillah	1/4/21 16:30	1/5/21 0:01	07:31:00					3666-O	21:35:00
31	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig Al Hail	1/5/21 5:30	1/5/21 20:00	14:30:00				Rig Diyina	3707	05:55:00
32	44197	44197.40625	3711	MAC PHOENIX	Half	1/1/21 21:22	Rig Muhaiyimat	1/2/21 4:13	1/2/21 15:45	11:32:00				Rig Hudairiyat	3708	15:06:00
33	44197	44197.40625	3711	MAC PHOENIX	Half	1/1/21 21:22	Deep Driller-3	1/2/21 17:30	1/3/21 12:15	18:45:00				Rig Makasib	4	07:05:00
34	44197	44197.33681	3712	ADNOC-812	Half	1/1/21 21:15	Barge Al Hyleh	1/2/21 9:45	1/2/21 18:10	08:25:00					6	12:10:00
35	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	Barge MB-1	1/2/21 10:45	1/2/21 15:50	05:05:00				Rig Marawwah	3710	14:15:00
36	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	Barge Pesto	1/3/21 8:30	1/3/21 13:25	04:55:00				Rig Muhaiyimat	3711	11:32:00
37	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	ZWSC	1/4/21 8:40	1/4/21 9:20	00:40:00				Rig SMS Faith	3710	02:48:00
38	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	ZCSC	1/4/21 13:40	1/4/21 17:45	04:05:00				Rig Sms Mariam	3	18:55:00
39	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	ZCSC	1/4/21 15:53	1/4/21 17:45	01:52:00				Rig VKN-3	3709	01:10:00
40	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	Barge MB-1	1/4/21 20:00	1/4/21 20:30	00:30:00				Rig Yemillah	6	01:30:00
41	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	ZWSC	1/5/21 8:40	1/5/21 11:45	03:05:00					3710	07:31:00
42	44197	44198.66667	1	LCT-TARFFAH	Full	1/3/21 7:10	Zirku Island	1/3/21 20:40	1/4/21 8:50	12:10:00				Umm Al Anbar	3706	03:42:00
43	44197	44198.66667	1	LCT-TARFFAH	Full	1/3/21 7:10	Ettouk Island	1/4/21 17:40	1/5/21 8:25	14:45:00				USSC	3705	07:25:00
44	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	ACPT	1/3/21 23:15	1/4/21 1:25	02:10:00				UZNSP	2	02:30:00
45	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	UZWSP	1/4/21 2:50	1/4/21 3:45	00:55:00				UZWSP	2	00:55:00
46	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	ACPT	1/4/21 8:30	1/4/21 18:30	10:00:00				ZCSC	3713	05:57:00
47	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	ACPT	1/5/21 7:25	1/5/21 11:50	04:25:00				Zirku Island	1	12:10:00
48	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	UZNSP	1/5/21 13:00	1/5/21 15:30	02:30:00				ZWSC	5	03:10:00
49	44197	44198	3	ADNOC-510	Full	1/2/21 14:20	Rig Sms Mariam	1/3/21 7:20	1/4/21 2:15	18:55:00					3713	03:45:00
50	44197	44197.90278	4	A-GRACE	Full	1/2/21 6:15	Rig Makasib	1/3/21 5:03	1/5/21 12:08	07:05:00
51	44197	44198.35417	5	MARCAP-2	Full	1/2/21 17:35	ZWSC	1/3/21 7:30	1/3/21 14:20	06:50:00
52	44197	44198.35417	5	MARCAP-2	Full	1/2/21 17:35	ZWSC	1/4/21 7:40	1/4/21 17:30	09:50:00
53	44197	44198.35417	5	MARCAP-2	Full	1/2/21 17:35	Barge Pesto	1/4/21 18:08	1/4/21 18:55	00:47:00
54	44197	44198.35417	5	MARCAP-2	Full	1/2/21 17:35	ZWSC	1/5/21 7:55	1/5/21 18:25	10:30:00
55	44197	44198.04167	6	QMS DELTA	Half	1/2/21 21:50	Rig Al Ghallan	1/3/21 10:50	1/3/21 11:25	00:35:00
56	44197	44198.04167	6	QMS DELTA	Half	1/2/21 21:50	Rig Yemillah	1/3/21 12:30	1/3/21 14:00	01:30:00
57	44197	44198.04167	6	QMS DELTA	Half	1/2/21 21:50	Rig Al Hail	1/4/21 1:10	1/4/21 17:30	16:20:00
58	44197	44198.04167	6	QMS DELTA	Half	1/2/21 21:50	Rig Makasib	1/5/21 19:30	1/6/21 7:40	12:10:00
59	44197	44198.43403	7	ADNOC-1011	Half	1/3/21 3:40	Al Qatia Island	1/3/21 19:20	1/3/21 22:00	02:40:00
60	44197	44198.43403	7	ADNOC-1011	Half	1/3/21 3:40	Bu Sikeen Island	1/4/21 7:10	1/4/21 10:00	02:50:00
61	44197	44198.43403	7	ADNOC-1011	Half	1/3/21 3:40	Al Qatia Island	1/4/21 13:40	1/4/21 22:30	08:50:00
62	44197	44198.43403	7	ADNOC-1011	Half	1/3/21 3:40	Al Ghallan Island	1/5/21 8:45	1/5/21 12:00	03:15:00
Sheet11

 

[Lukma]

Hi Etaf

Thanks so much, based on operation the voyage is given to a Vessel on trips, and when vessel return back it will be issued another voyage this process continue for every vessel and on same voyage vessel might go to different rigs for cargo operation so my idea is extract the unique Rig name L4 base on the voyage then give me the sum in M4

if you can see i have provide a sample of what am trying to achieve my trouble is to extract the unique name base on the voyage Auto with a formula same i have use sumifs to sum the actual time rigs has work.

though am unable t explain much but i believe the data will be okay with what i required to do

Like i said my main goal is to sum the actual time spent for each rigs on a voyage if you can see in column D you will notice that i voyage repeats even when the vessel visit the rigs twice or more so in column L4 i have i will regard USSC as one to give me all total Hours Spent for the voyage trips

Appreciate your support

Regards

Book21111.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
2			Criteria 2				Criteria 1
3	Month	Issued Month	Voyage No	Vessel Name	Deck Status	Departed	Rig Name	Start Time	End Time	Hours	Unique Rig Name per voyage No.	Actual Spent Hours on visit Voyage to Rig
4	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	02/01/21 08:55	02/01/21 12:15	3:20	USSC	7:25
5	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	Jopetwill-300	02/01/21 13:35	02/01/21 19:50	6:15	Jopetwill-300	6:15
6	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	02/01/21 20:25	02/01/21 21:30	1:05		0:00
7	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	03/01/21 07:45	03/01/21 10:45	3:00		0:00
8	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Al Ghallan Island	02/01/21 07:10	02/01/21 12:18	5:08	Al Ghallan Island	5:08
9	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Asseifiya Island	02/01/21 14:18	02/01/21 15:00	0:42	Asseifiya Island	0:42
10	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Ettouk Island	02/01/21 17:40	02/01/21 18:42	1:02	Ettouk Island	1:02
11	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Umm Al Anbar	03/01/21 07:00	03/01/21 10:42	3:42	Umm Al Anbar	3:42
12	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Deep Driller-3	04/01/21 04:18	04/01/21 16:42	12:24	Deep Driller-3	12:24
13	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Artabhatt-1	29/12/20 08:15	29/12/20 15:20	7:05	Rig Artabhatt-1	21:35
14	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Al Bzoom	30/12/20 15:50	30/12/20 21:00	5:10	Rig Al Bzoom	5:10
15	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Artabhatt-1	31/12/20 04:00	31/12/20 18:30	14:30		0:00
16	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Rig Artabhatt-1	02/01/21 09:45	02/01/21 10:55	1:10	Rig Artabhatt-1	1:10
17	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Barge Pride	02/01/21 13:40	02/01/21 16:30	2:50	Barge Pride	2:50
18	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Rig Diyina	02/01/21 20:45	03/01/21 02:40	5:55	Rig Diyina	5:55
19	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Deep Driller-3	03/01/21 10:15	03/01/21 12:10	1:55	Deep Driller-3	9:45
20	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Deep Driller-3	03/01/21 20:10	04/01/21 04:00	7:50		0:00
21	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Rig Hudairiyat	02/01/21 16:18	04/01/21 16:30	48:12	Rig Hudairiyat	63:06
22	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Rig Hudairiyat	04/01/21 21:00	05/01/21 11:54	14:54	Barge Shamal	4:06
23	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Barge Shamal	15/01/21 21:00	16/01/21 01:06	4:06	Rig Al Lulu	0:00
24	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Lulu	01/01/21 23:59	02/01/21 00:45	0:46	Rig Al Hail	1:35
25	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Hail	02/01/21 11:25	02/01/21 13:00	1:35	Rig VKN-3	1:10
26	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig VKN-3	02/01/21 22:00	02/01/21 23:10	1:10	Rig Al Ghallan	19:10
27	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Ghallan	03/01/21 08:35	04/01/21 03:45	19:10		0:00
28	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig SMS Faith	03/01/21 06:00	03/01/21 08:48	2:48	Rig SMS Faith	2:48
29	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Al Gharbia	03/01/21 12:00	03/01/21 21:00	9:00	Rig Al Gharbia	9:00
30	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Marawwah	04/01/21 00:35	04/01/21 14:50	14:15	Rig Marawwah	14:15
31	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Yemillah	04/01/21 16:30	05/01/21 00:01	7:31	Rig Yemillah	7:31
32	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Al Hail	05/01/21 05:30	05/01/21 20:00	14:30	Rig Al Hail	14:30
33	01-Jan-21	Jan-21	3711	MAC PHOENIX	Half	01/01/21 21:22	Rig Muhaiyimat	02/01/21 04:13	02/01/21 15:45	11:32	Rig Muhaiyimat	11:32
34	01-Jan-21	Jan-21	3711	MAC PHOENIX	Half	01/01/21 21:22	Deep Driller-3	02/01/21 17:30	03/01/21 12:15	18:45	Deep Driller-3	18:45
35	01-Jan-21	Jan-21	3712	ADNOC-812	Half	01/01/21 21:15	Barge Al Hyleh	02/01/21 09:45	02/01/21 18:10	8:25	Barge Al Hyleh	8:25
36	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge MB-1	02/01/21 10:45	02/01/21 15:50	5:05	Barge MB-1	5:35
37	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge Pesto	03/01/21 08:30	03/01/21 13:25	4:55	Barge Pesto	4:55
38	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZWSC	04/01/21 08:40	04/01/21 09:20	0:40	ZWSC	3:45
39	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZCSC	04/01/21 13:40	04/01/21 17:45	4:05	ZCSC	5:57
40	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZCSC	04/01/21 15:53	04/01/21 17:45	1:52		0:00
41	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge MB-1	04/01/21 20:00	04/01/21 20:30	0:30		0:00
42	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZWSC	05/01/21 08:40	05/01/21 11:45	3:05		0:00
43	01-Jan-21	Jan-21	1	LCT-TARFFAH	Full	03/01/21 07:10	Zirku Island	03/01/21 20:40	04/01/21 08:50	12:10	Zirku Island	12:10
44	01-Jan-21	Jan-21	1	LCT-TARFFAH	Full	03/01/21 07:10	Ettouk Island	04/01/21 17:40	05/01/21 08:25	14:45	Ettouk Island	14:45
45	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	03/01/21 23:15	04/01/21 01:25	2:10	ACPT	16:35
46	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	UZWSP	04/01/21 02:50	04/01/21 03:45	0:55	UZWSP	0:55
47	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	04/01/21 08:30	04/01/21 18:30	10:00	UZNSP	2:30
48	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	05/01/21 07:25	05/01/21 11:50	4:25		0:00
49	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	UZNSP	05/01/21 13:00	05/01/21 15:30	2:30		0:00
50	01-Jan-21	Jan-21	3	ADNOC-510	Full	02/01/21 14:20	Rig Sms Mariam	03/01/21 07:20	04/01/21 02:15	18:55	Rig Sms Mariam	18:55
51	01-Jan-21	Jan-21	4	A-GRACE	Full	02/01/21 06:15	Rig Makasib	03/01/21 05:03	05/01/21 12:08	55:05	Rig Makasib	55:05
52	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	03/01/21 07:30	03/01/21 14:20	6:50	ZWSC	27:10
53	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	04/01/21 07:40	04/01/21 17:30	9:50	Barge Pesto	0:47
54	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	Barge Pesto	04/01/21 18:08	04/01/21 18:55	0:47		0:00
55	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	05/01/21 07:55	05/01/21 18:25	10:30		0:00
56	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Al Ghallan	03/01/21 10:50	03/01/21 11:25	0:35	Rig Al Ghallan	0:35
57	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Yemillah	03/01/21 12:30	03/01/21 14:00	1:30	Rig Yemillah	1:30
58	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Al Hail	04/01/21 01:10	04/01/21 17:30	16:20	Rig Al Hail	16:20
59	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Makasib	05/01/21 19:30	06/01/21 07:40	12:10	Rig Makasib	12:10
60	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Qatia Island	03/01/21 19:20	03/01/21 22:00	2:40	Al Qatia Island	11:30
61	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Bu Sikeen Island	04/01/21 07:10	04/01/21 10:00	2:50	Bu Sikeen Island	2:50
62	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Qatia Island	04/01/21 13:40	04/01/21 22:30	8:50	Al Ghallan Island	3:15
63	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Ghallan Island	05/01/21 08:45	05/01/21 12:00	3:15		0:00
64	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	Barge Pesto	05/01/21 08:00	05/01/21 14:15	4:15	Barge Pesto	4:15
65	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	ZWSC	05/01/21 14:30	05/01/21 15:30	5:15	ZWSC	5:15
66	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	ACPT	05/01/21 17:00	05/01/21 18:40	6:15	ACPT	6:15
67	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	ZCSC	06/01/21 07:35	06/01/21 11:20	7:15	ZCSC	7:15
68	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	ZWSC	06/01/21 14:00	06/01/21 16:15
Sheet1

Cell Formulas
Range	Formula
M4:M67	M4	=IFERROR(SUMIFS($K$4:$K$68,$H$4:$H$68,L4,$D$4:$D$68,D4),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F68	Cell Value	contains "NAF"	text	NO
F64:F67	Cell Value	contains "NAF"	text	NO
F4:F63	Cell Value	contains "NAF"	text	NO

 

[etaf]

Although the results are in a different order, they are giving you the Time you want by Rig & Voyage.
I not around today , so will have to look at using formula's to layout in the way you want.
I suspect a combination of IF, COUNTIFS() & SUMIFS should work

 

[etaf]

in fact having written the above , i then thought it may be easier with the countifs etc

The COUNTIFS() range starts at the first cell and is locked into that range
COUNTIFS(G3:$G$3,G3,C3:$C$3,C3)
So G3 is fixed with $ and then as you copy down expands $G$3:G3 , $G$3:G4, $G$3:G5 etc, and only include RIG if the RIG & VOYAGE only appears once , hence count =1 , that will leave the RIG cell blank if it already exists above for that RIG & VOYAGE

the Time is formatted as [H]:MM to show hours greater than 24

chemistry_v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1			Criteria 2				Criteria 1
2	Month	Issued Month	Voyage	Vessel Name	Deck Status	Departed	Rig Name	Start Time	End Time	Hours	Rig Name	Actual Hours
3	44197	44197.03472	3705	ADNOC-810	Half	1/1/21 9:30	USSC	1/2/21 8:55	1/2/21 12:15	03:20:00	USSC	7:25
4	44197	44197.03472	3705	ADNOC-810	Half	1/1/21 9:30	Jopetwill-300	1/2/21 13:35	1/2/21 19:50	06:15:00	Jopetwill-300	6:15
5	44197	44197.03472	3705	ADNOC-810	Half	1/1/21 9:30	USSC	1/2/21 20:25	1/2/21 21:30	01:05:00
6	44197	44197.03472	3705	ADNOC-810	Half	1/1/21 9:30	USSC	1/3/21 7:45	1/3/21 10:45	03:00:00
7	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Al Ghallan Island	1/2/21 7:10	1/2/21 12:18	05:08:00	Al Ghallan Island	5:08
8	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Asseifiya Island	1/2/21 14:18	1/2/21 15:00	00:42:00	Asseifiya Island	0:42
9	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Ettouk Island	1/2/21 17:40	1/2/21 18:42	01:02:00	Ettouk Island	1:02
10	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Umm Al Anbar	1/3/21 7:00	1/3/21 10:42	03:42:00	Umm Al Anbar	3:42
11	44197	44196.95833	3706	ADNOC-224	Half	1/1/21 18:48	Deep Driller-3	1/4/21 4:18	1/4/21 16:42	12:24:00	Deep Driller-3	12:24
12	44166	44197.47917	3666-O	ASL SWIFT	Half	1/2/21 16:00	Rig Artabhatt-1	12/29/20 8:15	12/29/20 15:20	07:05:00	Rig Artabhatt-1	21:35
13	44166	44197.47917	3666-O	ASL SWIFT	Half	1/2/21 16:00	Rig Al Bzoom	12/30/20 15:50	12/30/20 21:00	05:10:00	Rig Al Bzoom	5:10
14	44166	44197.47917	3666-O	ASL SWIFT	Half	1/2/21 16:00	Rig Artabhatt-1	12/31/20 4:00	12/31/20 18:30	14:30:00
15	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Rig Artabhatt-1	1/2/21 9:45	1/2/21 10:55	01:10:00	Rig Artabhatt-1	1:10
16	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Barge Pride	1/2/21 13:40	1/2/21 16:30	02:50:00	Barge Pride	2:50
17	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Rig Diyina	1/2/21 20:45	1/3/21 2:40	05:55:00	Rig Diyina	5:55
18	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Deep Driller-3	1/3/21 10:15	1/3/21 12:10	01:55:00	Deep Driller-3	9:45
19	44197	44197.05208	3707	Z-POWER	Half	1/1/21 19:00	Deep Driller-3	1/3/21 20:10	1/4/21 4:00	07:50:00
20	44197	44197.35417	3708	SMIT LUZON	Full	1/1/21 20:30	Rig Hudairiyat	1/2/21 16:18	1/4/21 16:30	00:12:00	Rig Hudairiyat	63:06
21	44197	44197.35417	3708	SMIT LUZON	Full	1/1/21 20:30	Rig Hudairiyat	1/4/21 21:00	1/5/21 11:54	14:54:00
22	44197	44197.35417	3708	SMIT LUZON	Full	1/1/21 20:30	Barge Shamal	1/15/21 21:00	1/16/21 1:06	04:06:00	Barge Shamal	4:06
23	44197	44197.22222	3709	SMIT LUMUT	Half	1/1/21 19:15	Rig Al Lulu	1/1/21 23:59	1/2/21 0:45	00:46:00	Rig Al Lulu	0:46
24	44197	44197.22222	3709	SMIT LUMUT	Half	1/1/21 19:15	Rig Al Hail	1/2/21 11:25	1/2/21 13:00	01:35:00	Rig Al Hail	1:35
25	44197	44197.22222	3709	SMIT LUMUT	Half	1/1/21 19:15	Rig VKN-3	1/2/21 22:00	1/2/21 23:10	01:10:00	Rig VKN-3	1:10
26	44197	44197.22222	3709	SMIT LUMUT	Half	1/1/21 19:15	Rig Al Ghallan	1/3/21 8:35	1/4/21 3:45	19:10:00	Rig Al Ghallan	19:10
27	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig SMS Faith	1/3/21 6:00	1/3/21 8:48	02:48:00	Rig SMS Faith	2:48
28	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig Al Gharbia	1/3/21 12:00	1/3/21 21:00	09:00:00	Rig Al Gharbia	9:00
29	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig Marawwah	1/4/21 0:35	1/4/21 14:50	14:15:00	Rig Marawwah	14:15
30	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig Yemillah	1/4/21 16:30	1/5/21 0:01	07:31:00	Rig Yemillah	7:31
31	44197	44197.63125	3710	B-LIBERTY-313	NAF	1/2/21 12:45	Rig Al Hail	1/5/21 5:30	1/5/21 20:00	14:30:00	Rig Al Hail	14:30
32	44197	44197.40625	3711	MAC PHOENIX	Half	1/1/21 21:22	Rig Muhaiyimat	1/2/21 4:13	1/2/21 15:45	11:32:00	Rig Muhaiyimat	11:32
33	44197	44197.40625	3711	MAC PHOENIX	Half	1/1/21 21:22	Deep Driller-3	1/2/21 17:30	1/3/21 12:15	18:45:00	Deep Driller-3	18:45
34	44197	44197.33681	3712	ADNOC-812	Half	1/1/21 21:15	Barge Al Hyleh	1/2/21 9:45	1/2/21 18:10	08:25:00	Barge Al Hyleh	8:25
35	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	Barge MB-1	1/2/21 10:45	1/2/21 15:50	05:05:00	Barge MB-1	5:35
36	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	Barge Pesto	1/3/21 8:30	1/3/21 13:25	04:55:00	Barge Pesto	4:55
37	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	ZWSC	1/4/21 8:40	1/4/21 9:20	00:40:00	ZWSC	3:45
38	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	ZCSC	1/4/21 13:40	1/4/21 17:45	04:05:00	ZCSC	5:57
39	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	ZCSC	1/4/21 15:53	1/4/21 17:45	01:52:00
40	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	Barge MB-1	1/4/21 20:00	1/4/21 20:30	00:30:00
41	44197	44197.57639	3713	A-HERCULES	Half	1/2/21 0:30	ZWSC	1/5/21 8:40	1/5/21 11:45	03:05:00
42	44197	44198.66667	1	LCT-TARFFAH	Full	1/3/21 7:10	Zirku Island	1/3/21 20:40	1/4/21 8:50	12:10:00	Zirku Island	12:10
43	44197	44198.66667	1	LCT-TARFFAH	Full	1/3/21 7:10	Ettouk Island	1/4/21 17:40	1/5/21 8:25	14:45:00	Ettouk Island	14:45
44	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	ACPT	1/3/21 23:15	1/4/21 1:25	02:10:00	ACPT	16:35
45	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	UZWSP	1/4/21 2:50	1/4/21 3:45	00:55:00	UZWSP	0:55
46	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	ACPT	1/4/21 8:30	1/4/21 18:30	10:00:00
47	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	ACPT	1/5/21 7:25	1/5/21 11:50	04:25:00
48	44197	44199.01042	2	ADNOC-850	Half	1/3/21 13:15	UZNSP	1/5/21 13:00	1/5/21 15:30	02:30:00	UZNSP	2:30
49	44197	44198	3	ADNOC-510	Full	1/2/21 14:20	Rig Sms Mariam	1/3/21 7:20	1/4/21 2:15	18:55:00	Rig Sms Mariam	18:55
50	44197	44197.90278	4	A-GRACE	Full	1/2/21 6:15	Rig Makasib	1/3/21 5:03	1/5/21 12:08	07:05:00	Rig Makasib	55:05
51	44197	44198.35417	5	MARCAP-2	Full	1/2/21 17:35	ZWSC	1/3/21 7:30	1/3/21 14:20	06:50:00	ZWSC	27:10
52	44197	44198.35417	5	MARCAP-2	Full	1/2/21 17:35	ZWSC	1/4/21 7:40	1/4/21 17:30	09:50:00
53	44197	44198.35417	5	MARCAP-2	Full	1/2/21 17:35	Barge Pesto	1/4/21 18:08	1/4/21 18:55	00:47:00	Barge Pesto	0:47
54	44197	44198.35417	5	MARCAP-2	Full	1/2/21 17:35	ZWSC	1/5/21 7:55	1/5/21 18:25	10:30:00
55	44197	44198.04167	6	QMS DELTA	Half	1/2/21 21:50	Rig Al Ghallan	1/3/21 10:50	1/3/21 11:25	00:35:00	Rig Al Ghallan	0:35
56	44197	44198.04167	6	QMS DELTA	Half	1/2/21 21:50	Rig Yemillah	1/3/21 12:30	1/3/21 14:00	01:30:00	Rig Yemillah	1:30
57	44197	44198.04167	6	QMS DELTA	Half	1/2/21 21:50	Rig Al Hail	1/4/21 1:10	1/4/21 17:30	16:20:00	Rig Al Hail	16:20
58	44197	44198.04167	6	QMS DELTA	Half	1/2/21 21:50	Rig Makasib	1/5/21 19:30	1/6/21 7:40	12:10:00	Rig Makasib	12:10
59	44197	44198.43403	7	ADNOC-1011	Half	1/3/21 3:40	Al Qatia Island	1/3/21 19:20	1/3/21 22:00	02:40:00	Al Qatia Island	11:30
60	44197	44198.43403	7	ADNOC-1011	Half	1/3/21 3:40	Bu Sikeen Island	1/4/21 7:10	1/4/21 10:00	02:50:00	Bu Sikeen Island	2:50
61	44197	44198.43403	7	ADNOC-1011	Half	1/3/21 3:40	Al Qatia Island	1/4/21 13:40	1/4/21 22:30	08:50:00
62	44197	44198.43403	7	ADNOC-1011	Half	1/3/21 3:40	Al Ghallan Island	1/5/21 8:45	1/5/21 12:00	03:15:00	Al Ghallan Island	3:15
Sheet11

Cell Formulas
Range	Formula
K3	K3	=IF(COUNTIFS(G3:$G$3,G3,C3:$C$3,C3)=1,G3,"")
L3:L62	L3	=IF(K3="","",SUMIFS($J$3:$J$62,$G$3:$G$62,G3,$C$3:$C$62,C3))
K4:K62	K4	=IF(COUNTIFS(G$3:$G4,G4,C$3:$C4,C4)=1,G4,"")

 

[Lukma]

Hi etaf.

It so awesome seeing how amazing formula makes work easy in fact it just as i needed it, and it run so fast and make my task easy to extract am so so so happy, am happy joining the forum and learning more daily on excel.am so grateful..

well i have one more question which am ask to do after achieving the actual Time in column L4 now i created another column M4 to determine the KPI goals

Base on the KPI set is on Full & Half Deck so i need a formula that can give me the result of average time spent, that do not meet the KPI target only from column L3 based on Criteria in Column E.

The KPI set for average time spent on Full Deck cargo is 16:00
the KP set for average time spent on Half Deck cargo is 10:00

Presently I need to summarize the average monthly based with above KPI in Column K1 and K2 based criteria in yellow highlighted

Book21111.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	01-Jan-21			KPI	16:00	Average time spent on Full Deck		Full
2					KPI	10:00	Average time spent on Half Deck		Half
3	Month	Issued Month	Voyage No	Vessel Name	Deck Status	Departed	Rig Name	Start Time	End Time	Hours	Unique Rig Name per voyage No.	Actual Spent Hours on visit Voyage to Rig
4	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	02/01/21 08:55	02/01/21 12:15	3:20	USSC	7:25
5	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	Jopetwill-300	02/01/21 13:35	02/01/21 19:50	6:15	Jopetwill-300	6:15
6	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	02/01/21 20:25	02/01/21 21:30	1:05		0:00
7	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	03/01/21 07:45	03/01/21 10:45	3:00		0:00
8	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Al Ghallan Island	02/01/21 07:10	02/01/21 12:18	5:08	Al Ghallan Island	5:08
9	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Asseifiya Island	02/01/21 14:18	02/01/21 15:00	0:42	Asseifiya Island	0:42
10	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Ettouk Island	02/01/21 17:40	02/01/21 18:42	1:02	Ettouk Island	1:02
11	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Umm Al Anbar	03/01/21 07:00	03/01/21 10:42	3:42	Umm Al Anbar	3:42
12	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Deep Driller-3	04/01/21 04:18	04/01/21 16:42	12:24	Deep Driller-3	12:24
13	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Artabhatt-1	29/12/20 08:15	29/12/20 15:20	7:05	Rig Artabhatt-1	21:35
14	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Al Bzoom	30/12/20 15:50	30/12/20 21:00	5:10	Rig Al Bzoom	5:10
15	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Artabhatt-1	31/12/20 04:00	31/12/20 18:30	14:30		0:00
16	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Rig Artabhatt-1	02/01/21 09:45	02/01/21 10:55	1:10	Rig Artabhatt-1	1:10
17	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Barge Pride	02/01/21 13:40	02/01/21 16:30	2:50	Barge Pride	2:50
18	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Rig Diyina	02/01/21 20:45	03/01/21 02:40	5:55	Rig Diyina	5:55
19	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Deep Driller-3	03/01/21 10:15	03/01/21 12:10	1:55	Deep Driller-3	9:45
20	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Deep Driller-3	03/01/21 20:10	04/01/21 04:00	7:50		0:00
21	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Rig Hudairiyat	02/01/21 16:18	04/01/21 16:30	48:12	Rig Hudairiyat	63:06
22	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Rig Hudairiyat	04/01/21 21:00	05/01/21 11:54	14:54	Barge Shamal	4:06
23	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Barge Shamal	15/01/21 21:00	16/01/21 01:06	4:06	Rig Al Lulu	0:00
24	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Lulu	01/01/21 23:59	02/01/21 00:45	0:46	Rig Al Hail	1:35
25	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Hail	02/01/21 11:25	02/01/21 13:00	1:35	Rig VKN-3	1:10
26	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig VKN-3	02/01/21 22:00	02/01/21 23:10	1:10	Rig Al Ghallan	19:10
27	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Ghallan	03/01/21 08:35	04/01/21 03:45	19:10		0:00
28	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig SMS Faith	03/01/21 06:00	03/01/21 08:48	2:48	Rig SMS Faith	2:48
29	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Al Gharbia	03/01/21 12:00	03/01/21 21:00	9:00	Rig Al Gharbia	9:00
30	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Marawwah	04/01/21 00:35	04/01/21 14:50	14:15	Rig Marawwah	14:15
31	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Yemillah	04/01/21 16:30	05/01/21 00:01	7:31	Rig Yemillah	7:31
32	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Al Hail	05/01/21 05:30	05/01/21 20:00	14:30	Rig Al Hail	14:30
33	01-Jan-21	Jan-21	3711	MAC PHOENIX	Half	01/01/21 21:22	Rig Muhaiyimat	02/01/21 04:13	02/01/21 15:45	11:32	Rig Muhaiyimat	11:32
34	01-Jan-21	Jan-21	3711	MAC PHOENIX	Half	01/01/21 21:22	Deep Driller-3	02/01/21 17:30	03/01/21 12:15	18:45	Deep Driller-3	18:45
35	01-Jan-21	Jan-21	3712	ADNOC-812	Half	01/01/21 21:15	Barge Al Hyleh	02/01/21 09:45	02/01/21 18:10	8:25	Barge Al Hyleh	8:25
36	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge MB-1	02/01/21 10:45	02/01/21 15:50	5:05	Barge MB-1	5:35
37	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge Pesto	03/01/21 08:30	03/01/21 13:25	4:55	Barge Pesto	4:55
38	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZWSC	04/01/21 08:40	04/01/21 09:20	0:40	ZWSC	3:45
39	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZCSC	04/01/21 13:40	04/01/21 17:45	4:05	ZCSC	5:57
40	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZCSC	04/01/21 15:53	04/01/21 17:45	1:52		0:00
41	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge MB-1	04/01/21 20:00	04/01/21 20:30	0:30		0:00
42	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZWSC	05/01/21 08:40	05/01/21 11:45	3:05		0:00
43	01-Jan-21	Jan-21	1	LCT-TARFFAH	Full	03/01/21 07:10	Zirku Island	03/01/21 20:40	04/01/21 08:50	12:10	Zirku Island	12:10
44	01-Jan-21	Jan-21	1	LCT-TARFFAH	Full	03/01/21 07:10	Ettouk Island	04/01/21 17:40	05/01/21 08:25	14:45	Ettouk Island	14:45
45	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	03/01/21 23:15	04/01/21 01:25	2:10	ACPT	16:35
46	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	UZWSP	04/01/21 02:50	04/01/21 03:45	0:55	UZWSP	0:55
47	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	04/01/21 08:30	04/01/21 18:30	10:00	UZNSP	2:30
48	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	05/01/21 07:25	05/01/21 11:50	4:25		0:00
49	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	UZNSP	05/01/21 13:00	05/01/21 15:30	2:30		0:00
50	01-Jan-21	Jan-21	3	ADNOC-510	Full	02/01/21 14:20	Rig Sms Mariam	03/01/21 07:20	04/01/21 02:15	18:55	Rig Sms Mariam	18:55
51	01-Jan-21	Jan-21	4	A-GRACE	Full	02/01/21 06:15	Rig Makasib	03/01/21 05:03	05/01/21 12:08	55:05	Rig Makasib	55:05
52	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	03/01/21 07:30	03/01/21 14:20	6:50	ZWSC	27:10
53	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	04/01/21 07:40	04/01/21 17:30	9:50	Barge Pesto	0:47
54	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	Barge Pesto	04/01/21 18:08	04/01/21 18:55	0:47		0:00
55	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	05/01/21 07:55	05/01/21 18:25	10:30		0:00
56	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Al Ghallan	03/01/21 10:50	03/01/21 11:25	0:35	Rig Al Ghallan	0:35
57	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Yemillah	03/01/21 12:30	03/01/21 14:00	1:30	Rig Yemillah	1:30
58	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Al Hail	04/01/21 01:10	04/01/21 17:30	16:20	Rig Al Hail	16:20
59	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Makasib	05/01/21 19:30	06/01/21 07:40	12:10	Rig Makasib	12:10
60	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Qatia Island	03/01/21 19:20	03/01/21 22:00	2:40	Al Qatia Island	11:30
61	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Bu Sikeen Island	04/01/21 07:10	04/01/21 10:00	2:50	Bu Sikeen Island	2:50
62	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Qatia Island	04/01/21 13:40	04/01/21 22:30	8:50	Al Ghallan Island	3:15
63	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Ghallan Island	05/01/21 08:45	05/01/21 12:00	3:15		0:00
64	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	Barge Pesto	05/01/21 08:00	05/01/21 14:15	4:15	Barge Pesto	4:15
65	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	ZWSC	05/01/21 14:30	05/01/21 15:30	5:15	ZWSC	5:15
66	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	ACPT	05/01/21 17:00	05/01/21 18:40	6:15	ACPT	6:15
67	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	ZCSC	06/01/21 07:35	06/01/21 11:20	7:15	ZCSC	7:15
68	01-Jan-21	Jan-21	24	ADNOC-810	Half	04/01/21 16:20	ZWSC	06/01/21 14:00	06/01/21 16:15
Sheet1

Cell Formulas
Range	Formula
L4	L4	=IF(COUNTIFS(H4:$H$4,H4,D4:$D$4,D4)=1,H4,"")
L5:L7	L5	=IF(COUNTIFS(H$4:$H5,H5,D$4:$D5,D5)=1,H5,"")
M4	M4	=IF(L4="","",SUMIFS($K$4:$K$63,$H$4:$H$63,H4,$D$4:$D$63,D4))
M5:M67	M5	=IFERROR(SUMIFS($K$4:$K$68,$H$4:$H$68,L5,$D$4:$D$68,D5),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F68	Cell Value	contains "NAF"	text	NO
F64:F67	Cell Value	contains "NAF"	text	NO
F4:F63	Cell Value	contains "NAF"	text	NO

 

[etaf]

not sure i follow

Base on the KPI set is on Full & Half Deck so i need a formula that can give me the result of average time spent, that do not meet the KPI target only from column L3 based on Criteria in Column E.

The KPI set for average time spent on Full Deck cargo is 16:00
the KP set for average time spent on Half Deck cargo is 10:00

I dont know if this is a Target
or what average you are after ?
can you explain with examples

The average for all the times , FULL & HALF is

VARIOUS Sheets.xlsx
	O	P	Q	R
1	The KPI set for average time spent on Full Deck cargo is 16:00
2	the KP set for average time spent on Half Deck cargo is 10:00
3
4		Average?	Target
5
6	Full	17:49	16:00
7	Half	5:18	10:00
8
9
Sheet13

Cell Formulas
Range	Formula
P6	P6	=AVERAGEIF(F:F,"full",M:M)
P7	P7	=AVERAGEIF(F:F,"half",M:M)

 

[Lukma]

Hi Etaf

Thanks for your feedback and support the average am looking for is for two KPI for every month

1. the average hours exceeding the 16hrs. KPI target on Full, for example you will see for full remarks in column F4, so I need to find the average in Column M4 that exceed the KPI target base on criteria monthly and same goes for Half 10Hrs KPI

my aim to to find the average exceeding the target KPI monthly for both 16hrs on Full and 10Hrs on Half.

I hope this explanations is ok

Regards

 

[etaf]

really sorry not getting what the actual formula needs to be.

find the average exceeding the target KPI monthly for both 16hrs on Full and 10Hrs on Half.

Exceeding is the bit i'm struggling wiith and then average what ?

Row 12 is the first value in M (12.24 ) to exceed the Half KPI of 10 , that exceeds by 2.24 and then the average would be 2.24
Row 15 14.3 half , exceeds by 4.3 - so average of those 2 is 2.24+4.3 / 2
Row 21 63.06 FULL Target 16 , so exceeds target by 47.06 , then its average would be 47.06

I think i need in words some examples , so i can work out what you need

 

[Lukma]

Hi Etaf

I posted few table and am looking for this average for each in column P10, P11, P12, P13.

I try using the averageifs to determine the average based on criteria but i dont think am able to get it right

Appreciate if you could look around the formula

thanks

Book1ilsp.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
9	01-May-21	ILSP Performance Leads		Avrg Hrs		ILSP KPI Structure			No's Of Voyage	Total Avrg Hrs	Avrg Hrs
10	Avrg Time Spent on Full Deck Jackup Rigs <16			18:44		Acutual Visitt Trip w/ Full Deck J & Barges			11	206:10	18:44		Avergae Time	Rigs&Barges	#DIV/0!	Full	12:0
11	Avrg Time Spent on Half Deck Jackup Rigs <10			9:30		Acutual Visitt Trip w/ Half Deck J & Barges			46	437:45	9:30		Avergae Time	Rigs&Barges	#DIV/0!	Half	10:0
12	Avrg Time Spent on Drilling Island <10			4:29		Actual Operation Drilling Island			33	148:00	4:29		Averge Time	Drl-Island	14:56	target	10:0
13	Avrg Time Spent on Dlight Prd-Island Rigs <10			8:40		Actual Prd-Island Visit D/light			12	104:00	8:40	P	Averge Time	Prd-Island	14:28	Operation	10:0
14	Avrg Time Spent on D-light Complex <12			6:31		Actual Complex Visit D/light			28	182:41	6:31	P	Averge Time	Prd-Complex	19:20	Operation	12:0
15
16	Year	V-No	Supply	Manifest	D/P	Vessel Dept	Full	Location	Arrival & Start	Depature	Total		ILSP	Location	Total	KPI
17	Month	Mus-No	Supply Vessel	Issued Month	VSL	ADNOC Jetty Date & Time	Half	Site	Date & Time	Date & Time	Spent Hrs		KPI Structure	Site	Actual Spent Hrs	Target
18	01-Jan-21	3705	ADNOC-810	Jan-21	P	01/01/21 09:30	Half	USSC	02/01/21 08:55	02/01/21 12:15	3:20		Prd-Complex	USSC	7:25
19	01-Jan-21	3705	ADNOC-810	Jan-21	P	01/01/21 09:30	Half	Jopetwill-300	02/01/21 13:35	02/01/21 19:50	6:15		Prd-Complex	Jopetwill-300	6:15
20	01-Jan-21	3705	ADNOC-810	Jan-21	P	01/01/21 09:30	Half	USSC	02/01/21 20:25	02/01/21 21:30	1:05
21	01-Jan-21	3705	ADNOC-810	Jan-21	P	01/01/21 09:30	Half	USSC	03/01/21 07:45	03/01/21 10:45	3:00
22	01-Jan-21	3706	ADNOC-224	Dec-20	D	01/01/21 18:48	Half	Al Ghallan Island	02/01/21 07:10	02/01/21 12:18	5:08		Drl-Island	Al Ghallan Island	5:08
23	01-Jan-21	3706	ADNOC-224	Dec-20	D	01/01/21 18:48	Half	Asseifiya Island	02/01/21 14:18	02/01/21 15:00	0:42		Drl-Island	Asseifiya Island	0:42
24	01-Jan-21	3706	ADNOC-224	Dec-20	D	01/01/21 18:48	Half	Ettouk Island	02/01/21 17:40	02/01/21 18:42	1:02		Drl-Island	Ettouk Island	1:02
25	01-Jan-21	3706	ADNOC-224	Dec-20	D	01/01/21 18:48	Half	Umm Al Anbar	03/01/21 07:00	03/01/21 10:42	3:42		Drl-Island	Umm Al Anbar	3:42
26	01-Jan-21	3706	ADNOC-224	Dec-20	D	01/01/21 18:48	Half	Deep Driller-3	04/01/21 04:18	04/01/21 16:42	12:24		Rigs&Barges	Deep Driller-3	12:24
27	01-Dec-20	3666-O	ASL SWIFT	Jan-21	D	02/01/21 16:00	Half	Rig Artabhatt-1	29/12/20 08:15	29/12/20 15:20	7:05		Rigs&Barges	Rig Artabhatt-1	21:35
28	01-Dec-20	3666-O	ASL SWIFT	Jan-21	D	02/01/21 16:00	Half	Rig Al Bzoom	30/12/20 15:50	30/12/20 21:00	5:10		Rigs&Barges	Rig Al Bzoom	5:10
29	01-Dec-20	3666-O	ASL SWIFT	Jan-21	D	02/01/21 16:00	Half	Rig Artabhatt-1	31/12/20 04:00	31/12/20 18:30	14:30
30	01-Jan-21	3707	Z-POWER	Jan-21	D	01/01/21 19:00	Half	Rig Artabhatt-1	02/01/21 09:45	02/01/21 10:55	1:10		Rigs&Barges	Rig Artabhatt-1	1:10
31	01-Jan-21	3707	Z-POWER	Jan-21	D	01/01/21 19:00	Half	Barge Pride	02/01/21 13:40	02/01/21 16:30	2:50		Rigs&Barges	Barge Pride	2:50
32	01-Jan-21	3707	Z-POWER	Jan-21	D	01/01/21 19:00	Half	Rig Diyina	02/01/21 20:45	03/01/21 02:40	5:55		Rigs&Barges	Rig Diyina	5:55
33	01-Jan-21	3707	Z-POWER	Jan-21	D	01/01/21 19:00	Half	Deep Driller-3	03/01/21 10:15	03/01/21 12:10	1:55		Rigs&Barges	Deep Driller-3	9:45
34	01-Jan-21	3707	Z-POWER	Jan-21	D	01/01/21 19:00	Half	Deep Driller-3	03/01/21 20:10	04/01/21 04:00	7:50
35	01-Jan-21	3708	SMIT LUZON	Jan-21	D	01/01/21 20:30	Full	Rig Hudairiyat	02/01/21 16:18	04/01/21 16:30	48:12		Rigs&Barges	Rig Hudairiyat	63:06
36	01-Jan-21	3708	SMIT LUZON	Jan-21	D	01/01/21 20:30	Full	Rig Hudairiyat	04/01/21 21:00	05/01/21 11:54	14:54
37	01-Jan-21	3708	SMIT LUZON	Jan-21	D	01/01/21 20:30	Full	Barge Shamal	15/01/21 21:00	16/01/21 01:06	4:06		Rigs&Barges	Barge Shamal	4:06
38	01-Jan-21	3709	SMIT LUMUT	Jan-21	D	01/01/21 19:15	Half	Rig Al Lulu	01/01/21 23:59	02/01/21 00:45	0:46		Rigs&Barges	Rig Al Lulu	0:46
39	01-Jan-21	3709	SMIT LUMUT	Jan-21	D	01/01/21 19:15	Half	Rig Al Hail	02/01/21 11:25	02/01/21 13:00	1:35		Rigs&Barges	Rig Al Hail	1:35
40	01-Jan-21	3709	SMIT LUMUT	Jan-21	D	01/01/21 19:15	Half	Rig VKN-3	02/01/21 22:00	02/01/21 23:10	1:10		Rigs&Barges	Rig VKN-3	1:10
41	01-Jan-21	3709	SMIT LUMUT	Jan-21	D	01/01/21 19:15	Half	Rig Al Ghallan	03/01/21 08:35	04/01/21 03:45	19:10		Rigs&Barges	Rig Al Ghallan	19:10
42	01-Jan-21	3710	B-LIBERTY-313	Jan-21	D	02/01/21 12:45	NAF	Rig SMS Faith	03/01/21 06:00	03/01/21 08:48	2:48		Rigs&Barges	Rig SMS Faith	2:48
43	01-Jan-21	3710	B-LIBERTY-313	Jan-21	D	02/01/21 12:45	NAF	Rig Al Gharbia	03/01/21 12:00	03/01/21 21:00	9:00		Rigs&Barges	Rig Al Gharbia	9:00
44	01-Jan-21	3710	B-LIBERTY-313	Jan-21	D	02/01/21 12:45	NAF	Rig Marawwah	04/01/21 00:35	04/01/21 14:50	14:15		Rigs&Barges	Rig Marawwah	14:15
45	01-Jan-21	3710	B-LIBERTY-313	Jan-21	D	02/01/21 12:45	NAF	Rig Yemillah	04/01/21 16:30	05/01/21 00:01	7:31		Rigs&Barges	Rig Yemillah	7:31
46	01-Jan-21	3710	B-LIBERTY-313	Jan-21	D	02/01/21 12:45	NAF	Rig Al Hail	05/01/21 05:30	05/01/21 20:00	14:30		Rigs&Barges	Rig Al Hail	14:30
47	01-Jan-21	3711	MAC PHOENIX	Jan-21	D	01/01/21 21:22	Half	Rig Muhaiyimat	02/01/21 04:13	02/01/21 15:45	11:32		Rigs&Barges	Rig Muhaiyimat	11:32
48	01-Jan-21	3711	MAC PHOENIX	Jan-21	D	01/01/21 21:22	Half	Deep Driller-3	02/01/21 17:30	03/01/21 12:15	18:45		Rigs&Barges	Deep Driller-3	18:45
49	01-Jan-21	3712	ADNOC-812	Jan-21	P	01/01/21 21:15	Half	Barge Al Hyleh	02/01/21 09:45	02/01/21 18:10	8:25		Prd-Complex	Barge Al Hyleh	8:25
50	01-Jan-21	3713	A-HERCULES	Jan-21	P	02/01/21 00:30	Half	Barge MB-1	02/01/21 10:45	02/01/21 15:50	5:05		Prd-Complex	Barge MB-1	5:35
51	01-Jan-21	3713	A-HERCULES	Jan-21	P	02/01/21 00:30	Half	Barge Pesto	03/01/21 08:30	03/01/21 13:25	4:55		Prd-Complex	Barge Pesto	4:55
52	01-Jan-21	3713	A-HERCULES	Jan-21	P	02/01/21 00:30	Half	ZWSC	04/01/21 08:40	04/01/21 09:20	0:40		Prd-Complex	ZWSC	3:45
53	01-Jan-21	3713	A-HERCULES	Jan-21	P	02/01/21 00:30	Half	ZCSC	04/01/21 13:40	04/01/21 17:45	4:05		Prd-Complex	ZCSC	5:57
54	01-Jan-21	3713	A-HERCULES	Jan-21	P	02/01/21 00:30	Half	ZCSC	04/01/21 15:53	04/01/21 17:45	1:52
55	01-Jan-21	3713	A-HERCULES	Jan-21	P	02/01/21 00:30	Half	Barge MB-1	04/01/21 20:00	04/01/21 20:30	0:30
ILSP_KPI_Tracking_Data_Entry

Cell Formulas
Range	Formula
J10	J10	=IFERROR(COUNTIFS($AA$18:$AA$696, $H$16, $CY$18:$CY$696, ">"&$B$2),"")
K10	K10	=IFERROR(SUMIFS($CY$18:$CY$696,$AA$18:$AA$696,$H$16),"")
L10:L14	L10	=IFERROR(SUM(K10/J10),"")
J11	J11	=IFERROR(COUNTIFS($AA$18:$AA$696, $H$17, $CY$18:$CY$696, ">"&$B$2),"")
K11	K11	=IFERROR(SUMIFS($CY$18:$CY$696,$AA$18:$AA$696,$H$17),"")
J12	J12	=IFERROR(COUNT($CV$18:$CV$696<>0,$CV$18:$CV$696),"")
K12	K12	=IFERROR(SUM(CV18:CV696),"")
J13	J13	=IFERROR(COUNT($DB$18:$DB$696<>0,$DB$18:$DB$696),"")
K13	K13	=IFERROR(SUM(DB18:DB696),"")
J14	J14	=IFERROR(COUNT($DE$18:$DE$696<>0,$DE$18:$DE$696),"")
K14	K14	=IFERROR(SUM(DE18:DE696),"")
E10	E10	=IFERROR(AVERAGEIFS($CY$18:$CY$696,$AB$18:$AB$696,"<>0",$AA$18:$AA$696,$H$16),"")
E11	E11	=IFERROR(AVERAGEIFS($CY$18:$CY$696,$AB$18:$AB$696,"<>0",$AA$18:$AA$696,$H$17),"")
E12	E12	=IFERROR(AVERAGEIF(CV18:CV696,"<>0"),"")
E13	E13	=IFERROR(AVERAGEIF(DB18:DB696,"<>0"),"")
E14	E14	=IFERROR(AVERAGEIF(DE18:DE696,"<>0"),"")
P10	P10	=AVERAGEIFS(P18:P60000,B18:B60000,B9,H18:H60000,Q10,N18:N60000,O10,P18:P60000,"">R10)
P11	P11	=AVERAGEIFS(P18:P60000,B18:B60000,B9,H18:H60000,Q11,N18:N60000,O11,P18:P60000,"">R11)
P12	P12	=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O12,$B$18:$B$60000,B9,$P$18:$P$60000,">"&R12)
P13	P13	=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O13,$B$18:$B$60000,B9,$P$18:$P$60000,">"&R13)
P14	P14	=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O14,$B$18:$B$60000,B9,$P$18:$P$60000,">"&R14)
E18:E55	E18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,8,0),"")
F18:F55	F18	=IFERROR(VLOOKUP(D18,$S$18:$T$94,2,0),"")
G18:G55	G18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,11,0),"")
H18:H55	H18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,3,0),"")
N18:N55	N18	=IFERROR(VLOOKUP(O18,$EL$18:$EM$86,2,0),"")
O18	O18	=IF(COUNTIFS(I18:$I$18,I18,C18:$C$18,C18)=1,I18,"")
P18:P55	P18	=IF(O18="","",SUMIFS($L$18:$L$60000,$I$18:$I$60000,I18,$C$18:$C$60000,C18))
O19:O55	O19	=IF(COUNTIFS(I$18:$I19,I19,C$18:$C19,C19)=1,I19,"")
B18:B55	B18	=IFERROR(EOMONTH(J18,-1)+1,"")
L18:L55	L18	=IF(OR(ISBLANK(J18),ISBLANK(K18)), "", K18-J18)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H18:H7467	Cell Value	contains "NAF"	text	NO
H16:H17	Cell Value	contains "NAF"	text	NO
H16:H17	Cell Value	contains "M"	text	NO
H16:H17	Cell Value	contains "Naf"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B9	List	=$ACM$18:$ACM$209