Averageifs with multiple criteria based on KPI target on monthly

[Lukma]

Hi Guys

Am Really Grateful with everyone in forum, solving my trouble work easily, i have been able to update my Sheet, i need assistance as am not too sure with the average of my result in Colum P10, P11, P12, P13
My Data Sheet for my task to find the average of Hours for each criteria that exceed the KPI target, the idea is to find the average monthly, so i created a column N18 next to Column O18 to define which section the location site belongs to within the KPI And From Column Q10 & R10 i have create a Hours for each KPI, the idea for this KPI is to find the average for each Criteria that exceed the T KPI target Hours monthly

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

Appreciate if anyone could help out with this solution

Book1
	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
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:E41	E18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,8,0),"")
F18:F41	F18	=IFERROR(VLOOKUP(D18,$S$18:$T$94,2,0),"")
G18:G41	G18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,11,0),"")
H18:H41	H18	=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,3,0),"")
N18:N41	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:P41	P18	=IF(O18="","",SUMIFS($L$18:$L$60000,$I$18:$I$60000,I18,$C$18:$C$60000,C18))
O19:O41	O19	=IF(COUNTIFS(I$18:$I19,I19,C$18:$C19,C19)=1,I19,"")
B18:B41	B18	=IFERROR(EOMONTH(J18,-1)+1,"")
L18:L41	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