Need Help With a Formula reposting my Data

[Lukma]

Hi friends

I need you help with my spreadsheet as am reporting this

I have a KPI set for Jackup-Rigs/Barges in two ways
1. >=70% deck load on any Location show spend <=20hrs on rigs/barges
2. <=69 Deck Load on any Location show spend <=14hrs on rigs/barges
3 Complex is 12 hrs.
4 prd-Drilling 10hrs
5. drl-Island 10hrs

In Column M12 i have use a unique to extract the list of Location accordingly
In Column N12 i have use a unique to extract Percentage for the Location accordingly
In Column P12 i have use sumifs to sum the actual Hours for Each Voyage Spent

Now In Column Q12 i need a Formula that Will Subtract the KPI from the Actual Hours base of Criteria of The KPI percentage
AND IF >=70% to 100% should subtract 20hrs from the actual in Column P Jackup-Rig/Barges
AND IF <=69 % From 0% to 69% should subtract 14hrs from the actual in Column P for Jackup-Rig/Barges
AND IF Complex should subtract 12hrs from the actual in Column P for Complex
AND IF Prd-Island should subtract 10hrs from the actual in Column P for Complex
AND IF drl-Island should subtract 10hrs from the actual in Column P for Complex


Then in Column D4 i need to count numbers of Exceed in Column Q based on Criteria Range of Rigs/Barges and >=70% from 70% to 100%
Then in Column D5 i need to count numbers of Exceed in Column Q based on Criteria Range of Rigs/Barges and <=69% From 0% to 69%
Then in Column D6 i need to count numbers of Exceed in Column Q based on Criteria Range Of Complex
Then in Column D6 i need to count numbers of Exceed in Column Q based on Criteria Range Of Prd-Island
Then in Column D6 i need to count numbers of Exceed in Column Q based on Criteria Range Of Drl-Island

Then Column E4 i need to sum all hours that Exceed KPI in Column Q based on Criteria Range of Rigs/Barges and >=70%
Then Column E4 i need to sum all hours that Exceed KPI in Column Q based on Criteria Range of Rigs/Barges and <=69%
Then Column F4 i need get the average hours that Exceed KPI in Column Q based on Criteria Range of Rigs/Barges and >=70%
Then Column F4 i need get the average that Exceed KPI in Column Q based on Criteria Range of Rigs/Barges and <=69%

I hope am Able to explain better with the formula i required as a help from friends also i have Highlight an Example

Regards

ILSP Offshore Vessel Tracking Time Spent.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		ILSP	KPI	No's of Visit	Total Hours	Average hrs.
4	Rigs&Barges	JackRigs	>=70%
5	Rigs&Barges	JackRigs	<=69%
6	Complex	Complex	12Hrs
7	Drl-Island	Drl-Island	10 Hrs
8	Prd-Island	Prd-Island	10 Hrs
9		June	>=70%	<=69%
10		Hours	20	14	12	10
11		Month	Voyage	Vessel	Location	Deck Load %	Location Start Date & Time	Location End Date & Time	Total Time		Month	Voyage	Location	Deck %	Jackup Complex	Actual Time Spent	Exceed KPI
12	1310 ACPT	June	1310	ADNOC-850	ACPT	40%	01/06/22 10:10	01/06/22 12:00	1:50		June	1310	ACPT	40%	Complex	6:25
13	1310 Al Ghallan Island	June	1310	ADNOC-850	Al Ghallan Island	0%	01/06/22 13:40	01/06/22 14:20	0:40		June	1310	Al Ghallan Island	0%	Drl-Island	0:40
14	1310 ACPT	June	1310	ADNOC-850	ACPT	40%	01/06/22 15:30	01/06/22 15:50	0:20		June	1311	Rig Junana	80%	Rigs&Barges	22:25	2:25
15	1310 ACPT	June	1310	ADNOC-850	ACPT	40%	01/06/22 16:15	01/06/22 20:30	4:15		June	1311	Rig Marawwah	10%	Rigs&Barges	2:10
16	1311 Rig Junana	June	1311	M-SUPPORTER	Rig Junana	80%	01/06/22 03:50	01/06/22 22:00	18:10		June	1311	Rig Al Bzoom	0%	Rigs&Barges	6:45
17	1311 Rig Marawwah	June	1311	M-SUPPORTER	Rig Marawwah	10%	02/06/22 05:30	02/06/22 07:40	2:10		June	1311	Rig Al Yasat	0%	Rigs&Barges	1:20
18	1311 Rig Al Bzoom	June	1311	M-SUPPORTER	Rig Al Bzoom	0%	02/06/22 18:00	03/06/22 00:45	6:45		June	1312	ZNSAT	0%	Complex	0:50
19	1311 Rig Junana	June	1311	M-SUPPORTER	Rig Junana	80%	03/06/22 04:30	03/06/22 08:45	4:15		June	1312	ACPT	60%	Complex	10:40
20	1311 Rig Al Yasat	June	1311	M-SUPPORTER	Rig Al Yasat	0%	03/06/22 09:40	03/06/22 11:00	1:20		June	1312	Barge Leen	25%	Prd-Barge	2:30
21	1312 ZNSAT	June	1312	ADNOC-851	ZNSAT	0%	02/06/22 08:30	02/06/22 09:20	0:50		June	1313	Asseifiya Island	20%	Drl-Island	2:30
22	1312 ACPT	June	1312	ADNOC-851	ACPT	60%	02/06/22 11:05	02/06/22 15:30	4:25		June	1313	Al Ghallan Island	10%	Drl-Island	3:15
23	1312 ACPT	June	1312	ADNOC-851	ACPT	60%	02/06/22 17:10	02/06/22 19:50	2:40		June	1313	Ettouk Island	70%	Drl-Island	7:30
24	1312 ACPT	June	1312	ADNOC-851	ACPT	60%	02/06/22 21:20	03/06/22 00:10	2:50		June	1314	Al Qatia Island	0%	Drl-Island	6:30
25	1312 ACPT	June	1312	ADNOC-851	ACPT	60%	03/06/22 11:40	03/06/22 12:25	0:45		June	1314	Das Island	0%		10:15
26	1312 Barge Leen	June	1312	ADNOC-851	Barge Leen	25%	03/06/22 14:00	03/06/22 16:30	2:30		June	1315	Asseifiya Island	15%	Drl-Island	1:10
27	1313 Asseifiya Island	June	1313	ADNOC-1010	Asseifiya Island	20%	02/06/22 05:10	02/06/22 07:10	2:00		June	1315	Al Ghallan Island	15%	Drl-Island	6:22
28	1313 Al Ghallan Island	June	1313	ADNOC-1010	Al Ghallan Island	10%	02/06/22 13:45	02/06/22 17:00	3:15		June	1315	Umm Al Anbar	20%	Drl-Island	5:45
29	1313 Asseifiya Island	June	1313	ADNOC-1010	Asseifiya Island	20%	03/06/22 01:00	03/06/22 01:30	0:30		June	1315	Ettouk Island	50%	Drl-Island	6:27
30	1313 Ettouk Island	June	1313	ADNOC-1010	Ettouk Island	70%	03/06/22 21:45	04/06/22 05:15	7:30		June	1316	Al Qatia Island	30%	Drl-Island	0:24
31	1314 Al Qatia Island	June	1314	LCT-MARWAH-1	Al Qatia Island	0%	02/06/22 10:45	02/06/22 17:15	6:30		June	1316	Bu Sikeen Island	50%	Drl-Island	1:24
ILSP_Offshore_Vessel_Tracking

Cell Formulas
Range	Formula
A12:A31	A12	=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B31	B12	=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N32	K12	=UNIQUE(CHOOSE({1,2,3,4},B12:B8736,C12:C8736,E12:E8736,F12:F8736))
O12:O31	O12	=IFERROR(VLOOKUP(M12,Location,2,0),"")
P12:P31	P12	=IFERROR(1/(1/SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)),"")
F12:F31	F12	=IFERROR(VLOOKUP(A12,Info_Setting!$P$4:$S$600000,4,0),"0%")
I12:I31	I12	=IFERROR(1/(1/SUM([@[Location End Date & Time ]]-[@[Location Start Date & Time ]])),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B9	List	=Info_Setting!$K$3:$K$15

 

[KRice]

For reference, here is the earlier post related to this one...

Help With Correction of Formula in my Data.

Hi Friends Please i need a help with my formula in Cell D 4, 5, 6, 7, 8 my objective is to count base on Multiple criteria so 1. Count Cell Q base on Multiple Range O & N & K and Criteria But my Problem is that in the Range N is base on IF less that or equal to 69% and the other is If...

www.mrexcel.com