formula to extract actual average based on multiple criteria and ignore blank cell

[Lukma]

Hi Guys am not too sure with my average result i need a formula that can find the actual average for each multiple criteria monthly
my datasheet am looking for the average for each criteria
Average Time on column L14 for Full deck based on Jackup Rigs & barges Column H14
Average Time on column L14 for Half deck based on Jackup Rigs & barges Column H14
Average Time on column L14 for Island Rigs & barges Column H14
Average Time on column L14 for Complex Rigs & barges Column H14
Average Time on column L14 for Prd-Island Rigs & barges Column H14

=IFERROR(AVERAGEIFS($L$14:$L$61500,$G$14:$G$61500,$G$8,$H$14:$H$61500,$H$7,$B$14:$B$61500,$B$7),"")

ILSP Performance Tracking 2020.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3	HR		ILSP Level 1&2 Vessel Peformance Tracking 2020
4
5
6		Monthly Summary			ILSP Monthly Summary Report					Island Performan Leads			Percentage
7		01-Dec-20			Island			Jackup Rigs & Barge		Total Monthly Visit on Island		30
8		Avrg Time Full Deck			5:24	D	Full	14:20		Total Meet Target KPI		27	90%
9		Avrg Time Half Deck			6:02	D	Half	9:54		Total Exceed Target KPI		3	10%
10	T	0:01	10:10			0:01	10:10				10:00	16:10
11	T	10:00	200:00			8:00	11:00				16:10	200:00
12	M	Year	Manifest	Voyage	Supply	D/P	Full	Lang Rigs	Location	Arrival & Start	Depature	Total Hrs
13	Naf	Month	Issued Month	MUS No.	Vessel	VSL	Half	Rigs/Complex	Site	Date & Time	Date & Time	Spent
14		01-Nov-20	Nov-20	3135	B-HOMERE	D	Half	Jackup Rigs & Barge	Rig Muhaiyimat	01/11/20 21:45	01/11/20 22:15	0:30	0
15		01-Nov-20	Nov-20	3135	B-HOMERE	D	Half	Jackup Rigs & Barge	Rig Al Lulu	01/11/20 22:45	04/11/20 16:10	65:25	65
16		01-Nov-20	Nov-20	3136	ADNOC-222	D	Half	Jackup Rigs & Barge	Rig Shuweihat	04/11/20 18:00	05/11/20 01:35	7:35	7
17		01-Nov-20	Nov-20	3136	ADNOC-222	D	Half	Jackup Rigs & Barge	Barge Bahia	05/11/20 07:00	05/11/20 13:40	6:40	6
18		01-Nov-20	Nov-20	3136	ADNOC-222	D	Half	Jackup Rigs & Barge	Deep Driller-2	05/11/20 19:00	06/11/20 22:15	27:15	27
19		01-Nov-20	Nov-20	3136	ADNOC-222	D	Half	Jackup Rigs & Barge	Rig Artabhatt-1	07/11/20 00:10	07/11/20 02:00	1:50	1
20		01-Nov-20	Nov-20	3136	ADNOC-222	D	Half	Island	Umm Al Anbar	07/11/20 08:35	07/11/20 11:30	2:55	2
21		01-Nov-20	Nov-20	3137	MAC PHOENIX	D	Full	Jackup Rigs & Barge	Rig Sms Mariam	04/11/20 02:06	05/11/20 04:50	26:44	26
22		01-Nov-20	Nov-20	3137	MAC PHOENIX	D	Full	Jackup Rigs & Barge	Rig PN-8	05/11/20 06:06	05/11/20 10:24	4:18	4
23		01-Nov-20	Nov-20	3138	ADNOC-510	D	Half	Jackup Rigs & Barge	Rig Al Ghallan	01/11/20 20:45	02/11/20 18:10	21:25	21
24		01-Nov-20	Nov-20	3138	ADNOC-510	D	Half	Jackup Rigs & Barge	Deep Driller-2	02/11/20 19:35	03/11/20 01:25	5:50	5
25		01-Nov-20	Nov-20	3138	ADNOC-510	D	Half	Jackup Rigs & Barge	Deep Driller-3	03/11/20 12:15	03/11/20 20:20	8:05	8
26		01-Nov-20	Nov-20	3139	ADNOC-1011	D	Half	Island	Asseifiya Island	02/11/20 05:05	02/11/20 07:50	2:45	2
27		01-Nov-20	Nov-20	3139	ADNOC-1011	D	Half	Island	Umm Al Anbar	02/11/20 13:15	02/11/20 23:00	9:45	9
28		01-Nov-20	Nov-20	3139	ADNOC-1011	D	Half	Island	Ettouk Island	03/11/20 07:25	03/11/20 14:50	7:25	7
29		01-Nov-20	Nov-20	3139	ADNOC-1011	D	Half	Island	Umm Al Anbar	03/11/20 22:10	04/11/20 02:30	4:20	4
30		01-Nov-20	Nov-20	3140	ADNOC-1010	D	Half	Island	Bu Sikeen Island	02/11/20 19:20	03/11/20 04:40	9:20	9
31		01-Nov-20	Nov-20	3140	ADNOC-1010	D	Half	Island	Al Qatia Island	03/11/20 06:30	03/11/20 17:05	10:35	10
32		01-Nov-20	Nov-20	3141	LCT-TARFFAH	P	Full	Prd-Island	Arzanah Island	03/11/20 14:20	04/11/20 16:00	25:40	25
33		01-Nov-20	Nov-20	3141	LCT-TARFFAH	P	Full	Prd-Island	Zirku Island	04/11/20 21:25	05/11/20 15:35	18:10	18
34		01-Nov-20	Nov-20	3142	ADNOC-850	P	Half	Complex	ACPT	03/11/20 10:30	03/11/20 11:00	0:30	0
35		01-Nov-20	Nov-20	3142	ADNOC-850	P	Half	Jackup Rigs & Barge	Rig Artabhatt-1	03/11/20 12:15	03/11/20 12:50	0:35	0
36		01-Nov-20	Nov-20	3142	ADNOC-850	P	Half	Complex	Barge Al Ghweifat	04/11/20 12:50	04/11/20 19:15	6:25	6
37		01-Nov-20	Nov-20	3143	Z-OCEAN	P	Half	Complex	CPC-UAD	02/11/20 07:30	02/11/20 19:30	12:00	12
38		01-Nov-20	Nov-20	3143	Z-OCEAN	P	Half	Complex	Umm Lulu	03/11/20 06:42	03/11/20 18:00	11:18	11
39		01-Nov-20	Nov-20	3144	Z-EMPEROR	D	Half	Jackup Rigs & Barge	Rig Al Hail	03/11/20 12:40	03/11/20 22:40	10:00	10
40		01-Nov-20	Nov-20	3144	Z-EMPEROR	D	Half	Jackup Rigs & Barge	Rig VKN-3	03/11/20 23:40	05/11/20 00:00	24:20	24
41		01-Nov-20	Nov-20	3144	Z-EMPEROR	D	Half	Jackup Rigs & Barge	Barge Scirocco	05/11/20 11:45	06/11/20 22:40	34:55	34
42		01-Nov-20	Nov-20	3145	SEACOSCO OHIO	D	Naf	Jackup Rigs & Barge	Rig VKN-3	04/11/20 12:30	04/11/20 13:30	1:00	1
43		01-Nov-20	Nov-20	3145	SEACOSCO OHIO	D	Naf	Jackup Rigs & Barge	Rig Makasib	04/11/20 14:49	07/11/20 10:45	67:56	67
44		01-Nov-20	Nov-20	3145	SEACOSCO OHIO	D	Naf	Jackup Rigs & Barge	Rig VKN-3	07/11/20 12:05	07/11/20 15:16	3:11	3
45		01-Nov-20	Nov-20	3145	SEACOSCO OHIO	D	Naf	Jackup Rigs & Barge	Rig Makasib	08/11/20 00:13	11/11/20 21:11	92:58	92
46		01-Nov-20	Nov-20	3145	SEACOSCO OHIO	D	Naf	Jackup Rigs & Barge	Rig VKN-3	12/11/20 15:00	13/11/20 09:30	18:30	18
47		01-Nov-20	Nov-20	3145	SEACOSCO OHIO	D	Naf	Jackup Rigs & Barge	Rig Hudairiyat	13/11/20 11:00	13/11/20 17:10	6:10	6
48		01-Nov-20	Nov-20	3146	ADNOC-810	P	Half	Complex	USSC	02/11/20 17:25	02/11/20 19:20	1:55	1
49		01-Nov-20	Nov-20	3146	ADNOC-810	P	Half	Complex	ZWSC	03/11/20 07:35	03/11/20 08:10	0:35	0
50		01-Nov-20	Nov-20	3146	ADNOC-810	P	Half	Complex	ZWSC	04/11/20 07:40	04/11/20 08:00	0:20	0
51		01-Nov-20	Nov-20	3147	ADNOC-221	D	Half	Island	Al Ghallan Island	03/11/20 21:05	04/11/20 01:30	4:25	4
Backup Date

Cell Formulas
Range	Formula
L7	L7	=SUMIFS(R14:R73,N14:N73,N8)
L8	L8	=SUMIFS(P14:P73,N14:N73,N8)
M8:M9	M8	=L8/$L$7
L9	L9	=SUMIFS(Q14:Q73,N14:N73,N8)
E8	E8	=IFERROR(AVERAGEIFS($L$14:$L$61500,$F$14:$F$61500,$F$8,$H$14:$H$61500,$E$7,$B$14:$B$61500,$B$7),"")
E9	E9	=IFERROR(AVERAGEIFS($L$14:$L$61500,$G$14:$G$61500,$G$9,$H$14:$H$61500,$E$7,$B$14:$B$61500,$B$7),"")
H8	H8	=IFERROR(AVERAGEIFS($L$14:$L$61500,$G$14:$G$61500,$G$8,$H$14:$H$61500,$H$7,$B$14:$B$61500,$B$7),"")
H9	H9	=IFERROR(AVERAGEIFS($L$14:$L$61500,$G$14:$G$61500,$G$9,$H$14:$H$61500,$H$7,$B$14:$B$61500,$B$7),"")
F14:F51	F14	=IFERROR(VLOOKUP(E14,$IJ$17:$IK$96,2,0),"")
G14:G51	G14	=IFERROR(VLOOKUP(D14,$DM$14:$DQ$6017,5,0),"")
H14:H51	H14	=IFERROR(VLOOKUP(I14,$IB$17:$IE$280,2,0),"")
L14:L51	L14	=IF(OR(ISBLANK(J14),ISBLANK(K14)), "", K14-J14)
M14:M51	M14	=TEXT(ABS(K14-J14),"[h]")
B14:B51	B14	=IF(ISBLANK(C14)," ",DATE(YEAR(C14),MONTH(C14),0+1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G12:G1643	Cell Value	contains "M"	text	NO
G12:G1643	Cell Value	contains "Naf"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B7:D7	List	=Info_Data!$H$4:$H$32

 

[Fluff]

Duplicate to: Need assistance with a excel and solution formula tracking monthly summary

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread