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]

can you explain in words with examples , what you are trying to do
B10 for example AVERAGEIFS($CY$18:$CY$696,$AB$18:$AB$696,"<>0",$AA$18:$AA$696,$H$16
How does excel know its JACKUP RIGS ?
CY? AB? etc
No idea whats in those cells
you really need to explain in detail what you are trying to do - a XL2BB outout does not really tell me anything , other than what you have done - NOT what you want and why
Sorry - but the detail is what i need to help

 

[Lukma]

Hi Etaf

If you can see in column P10, P11, P12,P13 am trying to find the average for each base on the KPI

THE kpi is set to find the average of hours exceeding the target

But am not too sure with the averageifs i have use if am actually right with the result

ILSP KPI Performance Tracking.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
9	01-May-21	ILSP Performance Leads		Avrg Hrs		ILSP KPI Structure			No's Of Voyage	Total Avrg Hrs	Avrg Hrs		44197
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!	#DIV/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!	#DIV/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
13	Avrg Time Spent on Dlight Prd-Island Rigs <10			8:40		Actual Prd-Island Visit D/light			12	104:00	8:40		Averge Time	Prd-Island	14:28
14	Avrg Time Spent on D-light Complex <12			6:31		Actual Complex Visit D/light			28	182:41	6:31		Averge Time	Prd-Complex	12:24	19:2
15													16:00	10:00	12:00	10:00
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
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),"")
Q10	Q10	=AVERAGEIFS(P18:P35,B18:B35,B9,H18:H35,H16,N18:N35,O10,P18:P35,"">N15)
Q11	Q11	=AVERAGEIFS(P18:P60000,B18:B60000,B9,H18:H60000,H17,N18:N60000,O11,P18:P60000,"">O15)
P10	P10	=AVERAGEIFS(P18:P35,B18:B35,B9,H18:H35,H16,N18:N35,O10,P18:P35,"">N15)
P11	P11	=AVERAGEIFS(P18:P33,B18:B33,B9,H18:H33,H17,N18:N33,O11,P18:P33,"">O15)
P12	P12	=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O12,$B$18:$B$60000,B9,$P$18:$P$60000,">"&O15)
P13	P13	=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O13,$B$18:$B$60000,B9,$P$18:$P$60000,">"&O15)
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"),"")
P14	P14	=AVERAGEIFS(P18:P28,H18:H28,H17,B18:B28,N9,N18:N28,O11,P18:P28,">10:00")
Q14	Q14	=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O13,$B$18:$B$60000,B9,$P$18:$P$60000,">"&P15)
E18:E46	E18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,8,0),"")
F18:F46	F18	=IFERROR(VLOOKUP(D18,$S$18:$T$94,2,0),"")
G18:G46	G18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,11,0),"")
H18:H46	H18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,3,0),"")
N18:N46	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:P46	P18	=IF(O18="","",SUMIFS($L$18:$L$60000,$I$18:$I$60000,I18,$C$18:$C$60000,C18))
O19:O46	O19	=IF(COUNTIFS(I$18:$I19,I19,C$18:$C19,C19)=1,I19,"")
B18:B46	B18	=IFERROR(EOMONTH(J18,-1)+1,"")
L18:L46	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

 

[etaf]

sorry , i still have no idea what this could mean

THE kpi is set to find the average of hours exceeding the target

Need to see some worked examples, like i added

think of telling a kid what you are doing with words and why
again

If you can see in column P10, P11, P12,P13 am trying to find the average for each base on the KPI

Whats a BASE whats the KPI

You know what all these things are to you , and what they mean in your spreadsheet, i really don't, and sorry i'm not getting it

SO in words what are you trying to do , with worked examples - as if you are doing it on paper

Something like I put before, with a worked example and cell reference LONG hand

Really sorry again but None of your formulas make any sense

The sheet posted goes from B to Q
as mentioned
But your formulas reference AA, CY , DB, DK etc etc , I have no idea whats in those columns or why you are using

J10

J10

=IFERROR(COUNTIFS($AA$18:$AA$696, $H$16, $CY$18:$CY$696, ">"&$B$2),"")

I really do want to help , and not trying to be difficult, but unless i know in children type language , i'm not going to be able to

As its a new question, it maybe worth making this thread as solved and open a new one, maybe someone else will then read a new post and have a better idea of what you are trying to do

 

[Lukma]

Hi Etaf

Sorry for misunderstanding me, from start of my data i was able to get my spread sheet updated to above and i do really appreciate you help, Below is the KPI target set for operation
Average Time Spent on Full Deck Jackup Rigs <16
Average Time Spent on Half Deck Jackup Rigs <10
Average Time Spent on Drilling Island <10
Average Time Spent on Dlight Prd-Island Rigs <10
Average Time Spent on D-light Complex <12

so what am trying to achieve is the average hours that is greater than the target KPI in Column D3, D4, D5, D6 for Each Target Criteria KPI.

ILSP KPI Performance Tracking.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	01-Jan-21
2	Avergae Time	Rigs&Barges	#DIV/0!	Full
3	Avergae Time	Rigs&Barges	#DIV/0!	Half
4	Averge Time	Drl-Island	13:7
5	Averge Time	Prd-Island	12:10
6	Averge Time	Prd-Complex	21:52
7	16:00	10:00	12:00	10:00
8
9	Year	V-No	Supply	Manifest	D/P	Vessel Dept	Full	Location	Arrival & Start	Depature	Total		ILSP	Location	Total	KPI
10	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
11	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
12	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
13	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
14	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
15	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
16	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
17	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
18	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
19	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
20	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
21	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
22	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
23	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
24	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
25	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
26	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
27	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
28	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
29	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
30	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
31	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
32	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
33	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
34	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
35	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
36	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
37	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
38	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
39	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
40	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
41	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
42	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
43	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
44	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
45	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
46	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
47	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
48	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
49	01-Jan-21	3713	A-HERCULES	Jan-21	P	02/01/21 00:30	Half	ZWSC	05/01/21 08:40	05/01/21 11:45	3:05
50	01-Jan-21	1	LCT-TARFFAH	Jan-21	P	03/01/21 07:10	Full	Zirku Island	03/01/21 20:40	04/01/21 08:50	12:10		Prd-Island	Zirku Island	12:10
51	01-Jan-21	1	LCT-TARFFAH	Jan-21	P	03/01/21 07:10	Full	Ettouk Island	04/01/21 17:40	05/01/21 08:25	14:45		Drl-Island	Ettouk Island	14:45
52	01-Jan-21	2	ADNOC-850	Jan-21	P	03/01/21 13:15	Half	ACPT	03/01/21 23:15	04/01/21 01:25	2:10		Prd-Complex	ACPT	16:35
53	01-Jan-21	2	ADNOC-850	Jan-21	P	03/01/21 13:15	Half	UZWSP	04/01/21 02:50	04/01/21 03:45	0:55		Prd-Complex	UZWSP	0:55
54	01-Jan-21	2	ADNOC-850	Jan-21	P	03/01/21 13:15	Half	ACPT	04/01/21 08:30	04/01/21 18:30	10:00
55	01-Jan-21	2	ADNOC-850	Jan-21	P	03/01/21 13:15	Half	ACPT	05/01/21 07:25	05/01/21 11:50	4:25
56	01-Jan-21	2	ADNOC-850	Jan-21	P	03/01/21 13:15	Half	UZNSP	05/01/21 13:00	05/01/21 15:30	2:30		Prd-Complex	UZNSP	2:30
57	01-Jan-21	3	ADNOC-510	Jan-21	D	02/01/21 14:20	Full	Rig Sms Mariam	03/01/21 07:20	04/01/21 02:15	18:55		Rigs&Barges	Rig Sms Mariam	18:55
58	01-Jan-21	4	A-GRACE	Jan-21	D	02/01/21 06:15	Full	Rig Makasib	03/01/21 05:03	05/01/21 12:08	55:05		Rigs&Barges	Rig Makasib	55:05
59	01-Jan-21	5	MARCAP-2	Jan-21	P	02/01/21 17:35	Full	ZWSC	03/01/21 07:30	03/01/21 14:20	6:50		Prd-Complex	ZWSC	27:10
60	01-Jan-21	5	MARCAP-2	Jan-21	P	02/01/21 17:35	Full	ZWSC	04/01/21 07:40	04/01/21 17:30	9:50
61	01-Jan-21	5	MARCAP-2	Jan-21	P	02/01/21 17:35	Full	Barge Pesto	04/01/21 18:08	04/01/21 18:55	0:47		Prd-Complex	Barge Pesto	0:47
62	01-Jan-21	5	MARCAP-2	Jan-21	P	02/01/21 17:35	Full	ZWSC	05/01/21 07:55	05/01/21 18:25	10:30
63	01-Jan-21	6	QMS DELTA	Jan-21	D	02/01/21 21:50	Half	Rig Al Ghallan	03/01/21 10:50	03/01/21 11:25	0:35		Rigs&Barges	Rig Al Ghallan	0:35
64	01-Jan-21	6	QMS DELTA	Jan-21	D	02/01/21 21:50	Half	Rig Yemillah	03/01/21 12:30	03/01/21 14:00	1:30		Rigs&Barges	Rig Yemillah	1:30
65	01-Jan-21	6	QMS DELTA	Jan-21	D	02/01/21 21:50	Half	Rig Al Hail	04/01/21 01:10	04/01/21 17:30	16:20		Rigs&Barges	Rig Al Hail	16:20
66	01-Jan-21	6	QMS DELTA	Jan-21	D	02/01/21 21:50	Half	Rig Makasib	05/01/21 19:30	06/01/21 07:40	12:10		Rigs&Barges	Rig Makasib	12:10
67	01-Jan-21	7	ADNOC-1011	Jan-21	D	03/01/21 03:40	Half	Al Qatia Island	03/01/21 19:20	03/01/21 22:00	2:40		Drl-Island	Al Qatia Island	11:30
68	01-Jan-21	7	ADNOC-1011	Jan-21	D	03/01/21 03:40	Half	Bu Sikeen Island	04/01/21 07:10	04/01/21 10:00	2:50		Drl-Island	Bu Sikeen Island	2:50
69	01-Jan-21	7	ADNOC-1011	Jan-21	D	03/01/21 03:40	Half	Al Qatia Island	04/01/21 13:40	04/01/21 22:30	8:50
70	01-Jan-21	7	ADNOC-1011	Jan-21	D	03/01/21 03:40	Half	Al Ghallan Island	05/01/21 08:45	05/01/21 12:00	3:15		Drl-Island	Al Ghallan Island	3:15
71	01-Jan-21	8	QMS NEPTUNE	Jan-21	D	02/01/21 19:00	Half	Rig Muhaiyimat	02/01/21 23:45	03/01/21 01:50	2:05		Rigs&Barges	Rig Muhaiyimat	2:05
72	01-Jan-21	8	QMS NEPTUNE	Jan-21	D	02/01/21 19:00	Half	Rig SMS Faith	03/01/21 14:00	03/01/21 19:25	5:25		Rigs&Barges	Rig SMS Faith	5:25
73	01-Jan-21	8	QMS NEPTUNE	Jan-21	D	02/01/21 19:00	Half	Rig Yemillah	03/01/21 22:05	04/01/21 03:00	4:55		Rigs&Barges	Rig Yemillah	4:55
74	01-Jan-21	8	QMS NEPTUNE	Jan-21	D	02/01/21 19:00	Half	Rig Al Gharbia	04/01/21 06:30	04/01/21 17:25	10:55		Rigs&Barges	Rig Al Gharbia	10:55
75	01-Jan-21	8	QMS NEPTUNE	Jan-21	D	02/01/21 19:00	Half	Rig Qarnin	04/01/21 20:55	05/01/21 12:00	15:05		Rigs&Barges	Rig Qarnin	15:05
76	01-Jan-21	9	ADNOC-221	Jan-21	D	03/01/21 00:45	Half	Rig Artabhatt-1	03/01/21 09:45	03/01/21 14:25	4:40		Rigs&Barges	Rig Artabhatt-1	4:40
77	01-Jan-21	9	ADNOC-221	Jan-21	D	03/01/21 00:45	Half	Rig High Island-7	03/01/21 15:00	03/01/21 15:50	0:50		Rigs&Barges	Rig High Island-7	0:50
78	01-Jan-21	9	ADNOC-221	Jan-21	D	03/01/21 00:45	Half	Rig Sms Mariam	04/01/21 04:50	05/01/21 14:50	34:00		Rigs&Barges	Rig Sms Mariam	34:00
79	01-Jan-21	9	ADNOC-221	Jan-21	D	03/01/21 00:45	Half	Rig Junana	06/01/21 04:00	06/01/21 16:20	12:20		Rigs&Barges	Rig Junana	12:20
80	01-Jan-21	9	ADNOC-221	Jan-21	D	03/01/21 00:45	Half	Deep Driller-2	06/01/21 18:05	06/01/21 23:30	5:25		Rigs&Barges	Deep Driller-2	5:25
81	01-Jan-21	10	SEACOR ALPS	Jan-21	D	03/01/21 14:10	Full	Rig Butinah	04/01/21 19:30	05/01/21 18:05	22:35		Rigs&Barges	Rig Butinah	22:35
Sheet4

Cell Formulas
Range	Formula
D2	D2	=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E2,N11:N28,C2,P11:P28,"">B7)
D3	D3	=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E3,N11:N28,C2,P11:P28,"">B7)
D4	D4	=AVERAGEIFS($P$11:$P$59993,$N$11:$N$59993,C4,$B$11:$B$59993,B1,$P$11:$P$59993,">"&C7)
D5	D5	=AVERAGEIFS($P$11:$P$59993,$N$11:$N$59993,C5,$B$11:$B$59993,B1,$P$11:$P$59993,">"&E7)
D6	D6	=AVERAGEIFS($P$11:$P$59993,$N$11:$N$59993,C6,$B$11:$B$59993,B1,$P$11:$P$59993,">"&D7)
P11:P81	P11	=IF(O11="","",SUMIFS($L$11:$L$60000,$I$11:$I$60000,I11,$C$11:$C$60000,C11))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H11:H81	Cell Value	contains "NAF"	text	NO
H9:H10	Cell Value	contains "NAF"	text	NO
H9:H10	Cell Value	contains "M"	text	NO
H9:H10	Cell Value	contains "Naf"	text	NO

 

[etaf]

Average Time Spent on Full Deck Jackup Rigs <16

To Average the above in words is
Take the average of the Time Spent ( in Column P) , where the Criteria is "FULL" ( in column H ) And Criteria "Jackup Rigs" (NO IDEA which column has Jackup Rigs) and where Something is less than 16 ( No idea what column to find the less than 16 in )

your formula in D2

=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E2,N11:N28,C2,P11:P28,"">B7)

Take the average from column P, criteria column B dates that are equal to the cell in B1 which is the date , and must = 1/jan/21 and also look at the column H (FULL /HALF ) and Match E2 which is FULL , and also meet column N ILSP and match for C2 , rigs&barges , where the Time in P is ?????
Not sure what "">B7 , is that that to make it greater than 16:00 - ">"&B7

So that should be , note the red
=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E2,N11:N28,C2,P11:P28,">"&B7)

BUT the words say
Average Time Spent on Full Deck Jackup Rigs <16
Less than 16 - and in B2 you have greater than 16

if you want less than
=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E2,N11:N28,C2,P11:P28,"<"&B7)


Average Time Spent on Half Deck Jackup Rigs <10
Average Time Spent on Drilling Island <10
Average Time Spent on Dlight Prd-Island Rigs <10
Average Time Spent on D-light Complex <12

 

[Lukma]

Thanks So much Etaf

Yes i totally agreed with you though the write up was mess up by the writer not understanding what they require which i have giving my view to them

But in operation they are trying to get the location that have exceed the Hours of 16 hrs. to complete there work and then find the average hours they have spent

Well am so grateful for your time and patient and the suggest formula is right so am going to use that to define my average to them.

Thanks so much for your time.

REgards

 

[etaf]

glad we resolved the issue

 

[Lukma]

Hi Etaf

Thanks So Much well just one more and am really sorry bothering you, I need to a solution in Column F11 with IF formula
and my Idea is to Extract the exceed hours based on criteria of the KPI

I need a formula that subtract the criteria and get me only the Exceeded hours

IF column E11 is equal to Column C11 and matches D4 then it should subtract D5 from E11, and IF it match D5 then it should subtract F7 from E11 and If match D6 then it should Subtract E7 from E11 and IF E11 is Equal to C matches D2 and IF B11 Matches C8 then it should Subtract C7 from E11 and IF E11 equal to C11 and B11 equal to D8 then it should Subtract D7 from E11

I hope this can be done with if formula

ILSP KPI Performance Tracking.xlsx
	B	C	D	E	F
2		Avergae Time	Rigs&Barges
3		Avergae Time	Rigs&Barges
4		Averge Time	Drl-Island
5		Averge Time	Prd-Island
6		Averge Time	Prd-Complex
7		16:00	10:00	12:00	10:00
8		Full	Half
9		ILSP	Location	Total	KPI
10		KPI Structure	Site	Actual Spent Hrs	Target
11	Full	Prd-Complex	USSC	3:20
12	Full	Prd-Complex	Jopetwill-300	6:15
13	Full
14	Full
15	Full	Drl-Island	Al Ghallan Island	8:23
16	Full	Drl-Island	Asseifiya Island	0:42
17	Full	Drl-Island	Ettouk Island	15:47
18	Full	Drl-Island	Umm Al Anbar	3:42
19	Full	Rigs&Barges	Deep Driller-3	33:04
20	Full	Rigs&Barges	Rig Artabhatt-1	12:55
21	Full	Rigs&Barges	Rig Al Bzoom	5:10
22
23	Half	Rigs&Barges	Rig Artabhatt-1	12:55
24	Half	Rigs&Barges	Barge Pride	2:50
25	Half	Rigs&Barges	Rig Diyina	5:55
26	Half	Rigs&Barges	Deep Driller-3	33:04
27
28	Full	Rigs&Barges	Rig Hudairiyat	48:12
29
30	Full	Rigs&Barges	Barge Shamal	4:06
31	Full	Rigs&Barges	Rig Al Lulu	0:46
32	Full	Rigs&Barges	Rig Al Hail	32:25
33	Full	Rigs&Barges	Rig VKN-3	1:10
34	Full	Rigs&Barges	Rig Al Ghallan	19:45
35	Half	Rigs&Barges	Rig SMS Faith	8:13
36	Half	Rigs&Barges	Rig Al Gharbia	19:55
37	Half	Rigs&Barges	Rig Marawwah	14:15
38	Half	Rigs&Barges	Rig Yemillah	13:56
39	Half	Rigs&Barges	Rig Al Hail	32:25
40	Half	Rigs&Barges	Rig Muhaiyimat	13:37
41	Half	Rigs&Barges	Deep Driller-3	33:04
42	Half	Prd-Complex	Barge Al Hyleh	8:25
43	Half	Prd-Complex	Barge MB-1	5:05
44	Half	Prd-Complex	Barge Pesto	5:42
45	Half	Prd-Complex	ZWSC	7:30
46	Half	Prd-Complex	ZCSC	4:05
47	Half	Prd-Island	Zirku Island	12:10
48	Half	Prd-Complex	ACPT	2:10
49	Half	Prd-Complex	UZWSP	0:55
50	Half	Prd-Complex	UZNSP	2:30
51	Half	Rigs&Barges	Rig Sms Mariam	52:55
52	Half	Rigs&Barges	Rig Makasib	67:15
53	Half	Prd-Complex	ZWSC	7:30
54	Half	Prd-Complex	Barge Pesto	5:42
55	Half
56	Half	Rigs&Barges	Rig Al Ghallan	19:45
57	Half	Rigs&Barges	Rig Yemillah	13:56
58	Half	Rigs&Barges	Rig Al Hail	32:25
59	Half	Rigs&Barges	Rig Makasib	67:15
60	Half	Drl-Island	Al Qatia Island	2:40
61	Half	Drl-Island	Bu Sikeen Island	2:50
62	Half
63	Half	Drl-Island	Al Ghallan Island	8:23
64	Half	Rigs&Barges	Rig Muhaiyimat	13:37
65	Half	Rigs&Barges	Rig SMS Faith	8:13
66	Half	Rigs&Barges	Rig Yemillah	13:56
67	Half	Rigs&Barges	Rig Al Gharbia	19:55
68	Full	Rigs&Barges	Rig Qarnin	15:05
69	Full	Rigs&Barges	Rig Artabhatt-1	12:55
70	Full	Rigs&Barges	Rig High Island-7	0:50
71	Full	Rigs&Barges	Rig Sms Mariam	52:55
72	Full	Rigs&Barges	Rig Junana	12:20
73	Full	Rigs&Barges	Deep Driller-2	5:25
74	Full	Rigs&Barges	Rig Butinah	22:35
Sheet4

 

[etaf]

IF column E11 is equal to Column C11 and matches D4 then it should subtract D5 from E11, and IF it match D5 then it should subtract F7 from E11 and If match D6 then it should Subtract E7 from E11 and IF E11 is Equal to C matches D2 and IF B11 Matches C8 then it should Subtract C7 from E11 and IF E11 equal to C11 and B11 equal to D8 then it should Subtract D7 from E11

Nested IF = IF( TEST , TRUE , IF( TEST, TRUE , IF( TEST , TRUE , FALSE )))

IF column E11 is equal to Column C11 and matches D4 then it should subtract D5 from E11,
and IF it match D5 then it should subtract F7 from E11
and If match D6 then it should Subtract E7 from E11
and IF E11 is Equal to C matches D2 and IF B11 Matches C8 then it should Subtract C7 from E11
and IF E11 equal to C11 and B11 equal to D8 then it should Subtract D7 from E11

BUT

but the cells are text and time and your saying E11 - D5 E11 is 3:20 time - D5 which is a word prd-complex
so again, i dont know what you want , as that will never work.

 

[Lukma]

Thanks but what i need is to subtract the time if it meet the criteria KPI and to give me the Exceeded hours. in Column F11 and to drag it down

Rgeard