Need assistance with a excel and solution formula tracking monthly summary

[Lukma]

Hi Friends



Am Having an issue with work sheet I was assigned to keep the tracking of Vessel time spent base on different KPI



KPI as follows



Average time spent on Full Deck on Jack-up Rigs & Barges <16
Average Time Spent on Half Deck on Jack-up Rigs & Barges <10
Average time spent on drilling Island Operation <10
Average Time Spent on Production Complex <12
Average Time Spent on Production Island <10



Now I create a data on excel to keep the tracking for each vessel based on each voyage and time spent for each visited location site
I was able to use some formulas to keep this going on, but still am having issue with the summary and slowness to automatic generate my summary report for the month based on each KPI.

When you scroll to Right side I also created for each rigs vessel visit which gives me the total hours which vessel spent on time
That is if vessel visit site twice on her first voyage the total time will be added based on criteria of SUMIFS formula

Formula I have use on this report
SUMIFS
COUNTIFS
i Have also Use Index and Match and Countif to extract Unique of the repeated Voyage numbers

Now I need a help of a solution with this data sheet
1.Average time spent on Full Deck on Jack-up Rigs & Barges <16

Question : To find the total number of Visit for Full Deck for Each KPI criteria
Question : To Find The number that meet the KPI and Numbers that do not meet KPI target
Question : To find Percentage that meet KPI and that do not meet KPI
Question: To fine the average hours Spent that Meet KPI and That do not meet KPI


Please I need and urgent assistance with this Tracking data sheet ether with a formulas or VBA so I can easily generate my report for each month

Appreciate if anyone can work on better solution for me on this data.

 

[Lukma]

Hi Guys

Sorry i just got around on using the XL2BB and here file, i also have a link on drop box if anyone like to view and help out with better solution Dropbox Tracking Sheet.xlsx

	ILSP Level 1&2 Vessel Peformance Tracking 2020
Monthly Summary			ILSP Monthly Summary Report					Island Performan Leads			Percentage		Jackup & Barges Performance Leads		Percentage
01-Dec-20			Island			Jackup Rigs & Barge		Total Monthly Visit on Island		23			Total Monthly Visit Trips	28	Half
Avrg Time Full Deck			6:04	D	Full	15:20		Total Meet Target KPI		21	91%	S	Total Meet Target KPI	18	64%
Avrg Time Half Deck			5:39	D	Half	10:33		Total Exceed Target KPI		2	9%	N	Total Exceed Target KPI	10	36%
0:01	10:10			0:01	10:10				10:00	16:10
10:00	200:00			8:00	11:00				16:10	200:00
Year	Manifest	Voyage	Supply	D/P	Full	Lang Rigs	Location	Arrival & Start	Depature	Total Hrs			Supply	Island	Island	Total	Full <16	Full <16	Total	Half <10	Half <10	Total	Half	Total
Month	Issued Month	MUS No.	Vessel	VSL	Half	Rigs/Complex	Site	Date & Time	Date & Time	Spent			Vessels	Meet KPI Target	Exceed KPI Target	Visit	Meet KPI Jackup /Barge	Exceed KPI Jackup /Barge	Visit	Meet KPI Jackup / Barge	Exceed KPI Jackup / Barge	Visit	Jackup Rigs & Barge	Visit
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	S	ADNOC-1010
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	S	ADNOC-1011	9		9
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	S	ADNOC-221
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	S	ADNOC-222		1	1				2	1	3
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	S	ADNOC-223	1	1	2				2	1	3
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	S	ADNOC-224	2		2				1	1	2
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	N	ADNOC-225	1		1				5	2	7
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	N	ADNOC-226
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	N	ADNOC-227							7	3	10
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	N	ADNOC-228
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	N	ADNOC-229	1	1	2
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	N	ADNOC-230
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	S	ADNOC-510							3	2	5
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	S	ADNOC-511							4		4
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	S	ADNOC-512
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	S	A-CHLOE
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	S	AMS-RUBY							3	2	5
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	S	A-GRACE							4		4
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	S	A-RANGER							1	3	4
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	S	ASL SWIFT							2	3	5
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	S	A-TOURMALINE
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	S	B-Gulf-101
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	S	B-HOMERE							3	6	9
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	N	B-LIBERTY-313							1	1	2
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	S	CECILIE-K
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	S	LCT-WARDEH	3		3
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	S	MAC KESTREL							4	3	7
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	S	MAC PHOENIX							2	1	3
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	S	M-SUPPORTER							2	2	4
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	S	MUBARAK SPIRIT							2	1	3
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	N	QMS CARDINAL
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	S	QMS DELTA							1		1
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	N	QMS MARIMBA							2	1	3
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	S	QMS NEPTUNE							7		7
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	S	SEACOR ALPS
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	S	SEACOR PARANA							3		3
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	S	SEACOSCO OHIO
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	S	SMIT LUMUT
01-Nov-20	Nov-20	3147	ADNOC-221	D	Half	Island	Asseifiya Island	04/11/20 05:05	04/11/20 08:00	2:55	")]2	S	SMIT LUZON
01-Nov-20	Nov-20	3147	ADNOC-221	D	Half	Island	Ettouk Island	04/11/20 10:55	04/11/20 16:00	5:05	")]5	S	Z-EMPEROR							5	1	6
01-Nov-20	Nov-20	3147	ADNOC-221	D	Half	Island	Umm Al Anbar	04/11/20 18:00	04/11/20 23:20	5:20	")]5	S	Z-POWER	3		3				1		1
01-Nov-20	Nov-20	3147	ADNOC-221	D	Half	Jackup Rigs & Barge	Rig Muhaiyimat	05/11/20 05:10	05/11/20 12:30	7:20	")]7	S	Z-STAR
01-Nov-20	Nov-20	3148	QMS MARIMBA	D	Naf	Jackup Rigs & Barge	Rig High Island-7	02/11/20 19:30	03/11/20 11:35	16:05	")]16	S	S-COURAGEOUS								1	1
01-Nov-20	Nov-20	3148	QMS MARIMBA	D	Naf	Jackup Rigs & Barge	Rig Artabhatt-1	03/11/20 12:20	03/11/20 17:50	5:30	")]5	S	ADNOC-810
01-Nov-20	Nov-20	3148	QMS MARIMBA	D	Naf	Jackup Rigs & Barge	Rig Al Ghallan	05/11/20 09:20	05/11/20 11:30	2:10	")]2	S	ADNOC-811	1		1
01-Nov-20	Nov-20	3148	QMS MARIMBA	D	Naf	Jackup Rigs & Barge	Rig High Island-7	03/11/20 20:25	05/11/20 04:10	31:45	")]31	S	ADNOC-812
01-Nov-20	Nov-20	3149	AMS-RUBY	D	Half	Jackup Rigs & Barge	Rig SMS Faith	03/11/20 16:50	04/11/20 04:00	11:10	")]11	S	ADNOC-850
01-Nov-20	Nov-20	3149	AMS-RUBY	D	Half	Jackup Rigs & Barge	Barge Lulwa	04/11/20 06:00	04/11/20 07:15	1:15	")]1	S	ADNOC-851	1		1
01-Nov-20	Nov-20	3149	AMS-RUBY	D	Half	Jackup Rigs & Barge	Rig Al Gharbia	04/11/20 08:10	04/11/20 23:30	15:20	")]15	S	ADNOC-950
01-Nov-20	Nov-20	3150	MUBARAK SPIRIT	D	Full	Jackup Rigs & Barge	Rig Muhaiyimat	03/11/20 00:30	03/11/20 06:50	6:20	")]6	S	A-LIBERTY
01-Nov-20	Nov-20	3150	MUBARAK SPIRIT	D	Full	Jackup Rigs & Barge	Rig Al Lulu	03/11/20 07:50	03/11/20 20:30	12:40	")]12	S	A-RADIANT
01-Nov-20	Nov-20	3151	QMS CARDINAL	D	Half	Jackup Rigs & Barge	Rig Muhaiyimat	04/11/20 03:30	04/11/20 11:40	8:10	")]8	S	AMIL-61
01-Nov-20	Nov-20	3151	QMS CARDINAL	D	Half	Jackup Rigs & Barge	Deep Driller-3	04/11/20 12:00	05/11/20 04:40	16:40	")]16	S	LCT-MARWAH-1
01-Nov-20	Nov-20	3151	QMS CARDINAL	D	Half	Jackup Rigs & Barge	Rig Muhaiyimat	05/11/20 05:10	06/11/20 06:00	24:50	")]24	S	LCT-TARFFAH

 

[Lukma]

ILSP Performance Tracking 2020.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
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		Jackup & Barges Performance Leads		Percentage
7		01-Dec-20			Island			Jackup Rigs & Barge		Total Monthly Visit on Island		23			Total Monthly Visit Trips	28	Half
8		Avrg Time Full Deck			6:04	D	Full	15:20		Total Meet Target KPI		21	91%	S	Total Meet Target KPI	18	64%
9		Avrg Time Half Deck			5:39	D	Half	10:33		Total Exceed Target KPI		2	9%	N	Total Exceed Target KPI	10	36%
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			Supply	Island	Island	Total	Full <16	Full <16	Total	Half <10	Half <10	Total	Half	Total
13	Naf	Month	Issued Month	MUS No.	Vessel	VSL	Half	Rigs/Complex	Site	Date & Time	Date & Time	Spent			Vessels	Meet KPI Target	Exceed KPI Target	Visit	Meet KPI Jackup /Barge	Exceed KPI Jackup /Barge	Visit	Meet KPI Jackup / Barge	Exceed KPI Jackup / Barge	Visit	Jackup Rigs & Barge	Visit
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	S	ADNOC-1010
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	S	ADNOC-1011	9		9
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	S	ADNOC-221
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	S	ADNOC-222		1	1				2	1	3
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	S	ADNOC-223	1	1	2				2	1	3
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	S	ADNOC-224	2		2				1	1	2
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	N	ADNOC-225	1		1				5	2	7
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	N	ADNOC-226
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	N	ADNOC-227							7	3	10
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	N	ADNOC-228
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	N	ADNOC-229	1	1	2
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	N	ADNOC-230
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	S	ADNOC-510							3	2	5
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	S	ADNOC-511							4		4
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	S	ADNOC-512
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	S	A-CHLOE
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	S	AMS-RUBY							3	2	5
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	S	A-GRACE							4		4
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	S	A-RANGER							1	3	4
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	S	ASL SWIFT							2	3	5
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	S	A-TOURMALINE
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	S	B-Gulf-101
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	S	B-HOMERE							3	6	9
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	N	B-LIBERTY-313							1	1	2
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	S	CECILIE-K
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	S	LCT-WARDEH	3		3
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	S	MAC KESTREL							4	3	7
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	S	MAC PHOENIX							2	1	3
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	S	M-SUPPORTER							2	2	4
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	S	MUBARAK SPIRIT							2	1	3
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	N	QMS CARDINAL
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	S	QMS DELTA							1		1
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	N	QMS MARIMBA							2	1	3
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	S	QMS NEPTUNE							7		7
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	S	SEACOR ALPS
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	S	SEACOR PARANA							3		3
Backup Date

Cell Formulas
Range	Formula
L7	L7	=SUMIFS(R14:R73,N14:N73,N8)
P7	P7	=SUMIFS(X14:X692,AE14:AE692,G9)
L8	L8	=SUMIFS(P14:P73,N14:N73,N8)
M8:M9	M8	=L8/$L$7
L9	L9	=SUMIFS(Q14:Q73,N14:N73,N8)
P8	P8	=SUMIFS(V14:V692,AE14:AE692,G9)
Q8:Q9	Q8	=P8/$P$7
P9	P9	=SUMIFS(W14:W692,AE14:AE692,G9)
E8	E8	=IFERROR(AVERAGEIFS($L$14:$L$61483,$F$14:$F$61483,$F$8,$H$14:$H$61483,$E$7,$B$14:$B$61483,$B$7),"")
E9	E9	=IFERROR(AVERAGEIFS($L$14:$L$61483,$G$14:$G$61483,$G$9,$H$14:$H$61483,$E$7,$B$14:$B$61483,$B$7),"")
H8	H8	=IFERROR(AVERAGEIFS($L$14:$L$61483,$G$14:$G$61483,$G$8,$H$14:$H$61483,$H$7,$B$14:$B$61483,$B$7),"")
H9	H9	=IFERROR(AVERAGEIFS($L$14:$L$61483,$G$14:$G$61483,$G$9,$H$14:$H$61483,$H$7,$B$14:$B$61483,$B$7),"")
F14:F49	F14	=IFERROR(VLOOKUP(E14,$IJ$17:$IK$96,2,0),"")
G14:G49	G14	=IFERROR(VLOOKUP(D14,$DM$14:$DQ$6000,5,0),"")
H14:H49	H14	=IFERROR(VLOOKUP(I14,$IB$17:$IE$280,2,0),"")
L14:L49	L14	=IF(OR(ISBLANK(J14),ISBLANK(K14)), "", K14-J14)
M14:M49	M14	=TEXT(ABS(K14-J14),"[h]")
P14:P49	P14	=IFERROR(SUMIFS($BW$14:$BW$692,$AB$14:$AB$692,$O14),"")
Q14:Q49	Q14	=SUMIFS($BX$14:$BX$692,$AB$14:$AB$692,$O14)
R14:R49,X14:X49	R14	=SUM(P14:Q14)
V14:V49	V14	=SUMIFS($BZ$14:$BZ$692,$AB$14:$AB$692,O14)
W14:W49	W14	=SUMIFS($CA$14:$CA$692,$AB$14:$AB$692,O14)
S15	S15	=COUNTIF(AF15:AK15,"<Z11")
T14:T16	T14	=COUNTIFS(AF14:AK14,"<="&$T$11,AF14:AK14,">"&$S$11)
B14:B49	B14	=IF(ISBLANK(C14)," ",DATE(YEAR(C14),MONTH(C14),0+1))

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

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

 

[smozgur]

@Lukma - did you submit the last post just because you didn't include the formulas in the first XL2BB mini-sheet?

In that case, I will merge it into the previous post. Please let me know.

 

[Lukma]

No is just that i have a presentation by Monday with my Boss and i really don't want to look disappointed that's why am aiming to extract or a solution to be sure with the average of those main criteria Name Range i mentioned

Like i said earlier am fans of Mr. Excel on YouTube and many other lesson so i was figuring to have a help out with my Data work to make it more easier and fast to generation once i click on month drop down

in addition am not too familiar with the site yet to read most content rules and i felt bad posting same question

Hence i have not really got a good solution to my data and formula to run result faster and easier that why i was asking for solution on my average which i have paste the formula which i have used on it

thanks and i hope you understand me
i appreciate the forum for support