Help With Correction of Formula in my Data.

[Lukma]

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 greater or quall to 70% i was able to tray but i think am missing out something Please if any one could help to Check all my formula and to assist me with it.

Thanks

ILSP Offshore Vessel Tracking Time Spent-2022.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%	16	269:35	16:50
5	Rigs&Barges	JackRigs	69%	44	302:30	6:52
6	Complex	Complex	12Hrs	22	143:31	6:31
7	Drl-Island	Drl-Island	10 Hrs	33	175:50	5:19
8	Prd-Island	Prd-Island	10 Hrs	5	7:35	1:31
9		June	70%	69%
10		ILSP KPI	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	1044 ABK Complex	April	1044	Z-QUEEN	ABK Complex	80%	27/04/22 07:10	27/04/22 08:02	0:52		April	1044	ABK Complex	80%	Complex	26:46	14:46
13	1044 ABK Complex	April	1044	Z-QUEEN	ABK Complex	80%	27/04/22 09:40	27/04/22 09:48	0:08		May	1044	ABK Complex	80%	Complex	26:46	14:46
14	1044 ABK Complex	April	1044	Z-QUEEN	ABK Complex	80%	28/04/22 07:10	28/04/22 07:52	0:42		May	1044	Mutawa-202	0%		0:15
15	1044 ABK Complex	April	1044	Z-QUEEN	ABK Complex	80%	28/04/22 08:15	28/04/22 08:23	0:08		April	1045	Rig Vivekanand-3	100%	Rigs&Barges	63:00	43:00
16	1044 ABK Complex	May	1044	Z-QUEEN	ABK Complex	80%	01/05/22 06:50	01/05/22 07:07	0:17		April	1045	Rig Yemillah	0%	Rigs&Barges	12:10
17	1044 ABK Complex	May	1044	Z-QUEEN	ABK Complex	80%	01/05/22 08:25	02/05/22 08:33	24:08		April	1046	Nasr Complex	0%		10:15
18	1044 Mutawa-202	May	1044	Z-QUEEN	Mutawa-202	0%	01/05/22 14:30	01/05/22 14:45	0:15		April	1046	Barge Pepper	20%	Prd-Barge	2:30
19	1044 ABK Complex	May	1044	Z-QUEEN	ABK Complex	80%	02/05/22 08:55	02/05/22 09:14	0:19		April	1047	Al Qatia Island	50%	Drl-Island	2:34
20	1044 ABK Complex	May	1044	Z-QUEEN	ABK Complex	80%	02/05/22 10:40	02/05/22 10:52	0:12		April	1047	Bu Sikeen Island	20%	Drl-Island	0:33
21	1045 Rig Vivekanand-3	April	1045	QMS MARIMBA	Rig Vivekanand-3	100%	27/04/22 15:30	27/04/22 20:10	4:40		April	1048	Al Ghallan Island	10%	Drl-Island	0:20
22	1045 Rig Yemillah	April	1045	QMS MARIMBA	Rig Yemillah	0%	27/04/22 22:20	28/04/22 10:30	12:10		April	1048	Umm Al Anbar	30%	Drl-Island	10:50	0:50
23	1045 Rig Vivekanand-3	April	1045	QMS MARIMBA	Rig Vivekanand-3	100%	28/04/22 12:00	30/04/22 22:20	58:20		April	1048	Asseifiya Island	10%	Drl-Island	5:30
24	1046 Nasr Complex	April	1046	ADNOC-810	Nasr Complex	0%	27/04/22 08:00	27/04/22 18:15	10:15		April	1048	Ettouk Island	60%	Drl-Island	8:35
25	1046 Barge Pepper	April	1046	ADNOC-810	Barge Pepper	20%	27/04/22 21:30	28/04/22 00:00	2:30		April	1049	Rig Junana	0%	Rigs&Barges	0:05
26	1047 Al Qatia Island	April	1047	LCT-WARDEH	Al Qatia Island	50%	28/04/22 02:00	28/04/22 02:40	0:40		April	1049	Rig SMS Esse	0%	Rigs&Barges	19:15
ILSP_Offshore_Vessel_Tracking

Cell Formulas
Range	Formula
D4	D4	=COUNTIFS($Q$12:$Q$900000,">0:0",$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9,$N$12:$N$900000,">=70%")
E4	E4	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,">=70%")
F4:F8	F4	=E4/D4
D5	D5	=COUNTIFS($Q$12:$Q$900000,">=0",$O$12:$O$900000,$A$5,$K$12:$K$900000,$B$9,$N$12:$N$900000,"<=69%")
E5	E5	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,"<=69%")
D6	D6	=COUNTIFS($O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E6	E6	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9)
D7	D7	=COUNTIFS($O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E7	E7	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9)
D8	D8	=COUNTIFS($O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E8	E8	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9)
A12:A26	A12	=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B26	B12	=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N1739	K12	=UNIQUE(CHOOSE({1,2,3,4},B12:B2605,C12:C2605,E12:E2605,F12:F2605))
O12:O26	O12	=IFERROR(VLOOKUP(M12,Location,2,0),"")
P12:P26	P12	=IFERROR(1/(1/SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)),"")
Q12:Q26	Q12	=IF(AND(N12>=$C$4,O12=$A$4,P12>$C$10),P12-$C$10,IF(AND(N12<=$C$5,O12=$A$5,P12>$D$10),P12-$D$10,IF(AND(O12="",O12=$A$4,P12>$D$10),P12-$D$10,IF(AND(O12=$A$6,P12>$E$10),P12-$E$10,IF(AND(O12=$A$7,P12>$F$10),P12-$F$10,IF(AND(O12=$A$8,P12>$F$10),P12-$F$10,""))))))
F12:F26	F12	=IFERROR(VLOOKUP(A12,Info_Setting!$P$4:$S$600000,4,0),"0%")
I12:I26	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]

I have a few questions right now:

1. How long is your worksheet...or rather, as time goes on, how many rows would you expect it not to exceed?
2. In the O12 formula (and down), the name "Location" is mentioned. What is that? It appears to be at least a two-column array, but I don't see it defined anywhere. Consequently, my sheet shows all blanks in this column.
3. In the F12 formula (and down), a worksheet named Info_Setting is referenced. Are all of the values in column P (P4 and down) in ascending order, no exceptions? Right now, my sheet shows 0% in this column because the reference is not available. I can hardwire the values from your mini-table, but wanted to confirm that VLOOKUP should not produce incorrect results due to any potential sorting issue...this is why I rarely use VLOOKUP.
4. In O18, I see Prd-Barge. I don't see any matching items in A4:A8. Should I?

 

[KRice]

In Q12 (and down), the part highlighted in red leads to a condition that can never be TRUE unless $A$4 and O12 are blank. What is this part of the formula supposed to be doing? Is the 1st O12="" supposed to be N12=""?
=IF(AND(N12>=$C$4,O12=$A$4,P12>$C$10),P12-$C$10,IF(AND(N12<=$C$5,O12=$A$5,P12>$D$10),P12-$D$10,IF(AND(O12="",O12=$A$4,P12>$D$10),P12-$D$10,IF(AND(O12=$A$6,P12>$E$10),P12-$E$10,IF(AND(O12=$A$7,P12>$F$10),P12-$F$10,IF(AND(O12=$A$8,P12>$F$10),P12-$F$10,""))))))

 

[KRice]

How would a Deck % of 69.5% be categorized? It doesn't meet either of the Rigs&Barges criteria as currently written.

 

[Lukma]

Hi Krice

Thank you for for you feedback
well the KPI is set for 70%-100% deck load working hours 20hrs
and KPI for 0%-69% deck load working hours is 14 Hrs.

In Column Q i use this formula once the Actual hours in column P exceed any of the KPI then it should subtract hours to get the exceeding hours left in Q
now my trouble is the countifs to count the column Q based the KPI criteria in column D

Regards

 

[KRice]

In my posts 2-4 I've asked 7 questions. Some of those questions and comments point out where I think some problems exist. Other questions would help me understand how your worksheet is supposed to work. Please review those questions and answer them if possible. I do not understand your answer about the Deck %. Does your answer mean that you could never see a deck percentage between 69% and 70%?

 

[Lukma]

Krice

Sorry for the delay in reply and wasn't feeling to strong and also my PC wasn't fix at work but now am able to reply
thanks for your feedback and am reporting my data to clear more on the formula i required

Column C4 KPI is if >=70% to 100% should Spent 20hrs on Jack-up Rigs/ Barges
Column C5 KPI is if <= 69% to 0% should Spent 14hrs on Jack-up Rigs/ Barges
Column C6 KPI for Complex should Spent 12hrs on Complex
Column C7 KPI is should Spent 10hrs on Drl-Island
Column C8 KPI is should Spent 10hrs on Prd-Island
Column P12 is the actual hours spent on each location sites
Column Q12 i need the formula subtract if the any location that exceed the KPI criteria and give me the exceeded hours in Column Q12

Then From Column D4 to Count numbers of Exceed based on Criteria of >=70% upward from Column Q based on Criteria
And Column E4 to Sum the total hours based on Criteria of <=69% downward from Column Q based on Criteria

My problem now is the formula in Column Q,, D, E, and F

Appreciate if my Explanation is Cleared looking forward to a solution.

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%	1	8:25	8:25
5	Rigs&Barges	JackRigs	<=69%	0	0:00	#DIV/0!
6	Complex	Complex	12Hrs	0	0:00	#DIV/0!
7	Drl-Island	Drl-Island	10 Hrs	0	0:00	#DIV/0!
8	Prd-Island	Prd-Island	10 Hrs	0	0:00	#DIV/0!
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	8: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
D4	D4	=COUNTIFS($Q$12:$Q$900000,">0:0",$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9,$N$12:$N$900000,C4)
E4	E4	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,">=70%")
F4:F8	F4	=E4/D4
D5	D5	=COUNTIFS($Q$12:$Q$900000,">0",$O$12:$O$900000,$A$5,$K$12:$K$900000,$B$9,$N$12:$N$900000,C5)
E5	E5	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,"<=69%")
D6	D6	=COUNTIFS($O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E6	E6	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9)
D7	D7	=COUNTIFS($O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E7	E7	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9)
D8	D8	=COUNTIFS($O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E8	E8	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9)
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)),"")
Q12:Q31	Q12	=IF(AND(N12>=$C$4,O12=$A$4,P12>$C$10),P12-$C$10,IF(AND(N12<=$C$5,O12=$A$5,P12>$D$10),P12-$D$10,IF(AND(O12="",O12=$A$4,P12>$D$10),P12-$D$10,IF(AND(O12=$A$6,P12>$E$10),P12-$E$10,IF(AND(O12=$A$7,P12>$F$10),P12-$F$10,IF(AND(O12=$A$8,P12>$F$10),P12-$F$10,""))))))
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

 

[Lukma]

In Q12 (and down), the part highlighted in red leads to a condition that can never be TRUE unless $A$4 and O12 are blank. What is this part of the formula supposed to be doing? Is the 1st O12="" supposed to be N12=""?
=IF(AND(N12>=$C$4,O12=$A$4,P12>$C$10),P12-$C$10,IF(AND(N12<=$C$5,O12=$A$5,P12>$D$10),P12-$D$10,IF(AND(O12="",O12=$A$4,P12>$D$10),P12-$D$10,IF(AND(O12=$A$6,P12>$E$10),P12-$E$10,IF(AND(O12=$A$7,P12>$F$10),P12-$F$10,IF(AND(O12=$A$8,P12>$F$10),P12-$F$10,""))))))

good Day Krice

Thank you for reply the red for zero which i have use VLOOKUP to determine the site which do not have deck percentage as Zero %

But actually am not to sure about my formula that's why i request for a solution

i have also explain further in my second post

Appreciate your help with a solution

Regards

 

[KRice]

I'm sorry, but I do not understand what you are trying to do. I have asked numerous questions that might provide some clarity to me, but you have not answered any of those questions. Please review my post #6 and see that it references previous posts where the questions are asked. Here are two of the more critical questions...let's start with answers to these:

In the O12 formula (and down), the name "Location" is mentioned. What is that? It appears to be at least a two-column array, but I don't see it defined anywhere. Consequently, my sheet shows all blanks in this column.

In O18, I see Prd-Barge. I don't see any matching items in A4:A8. Should I?

 

[KRice]

I see that you posted a near duplicate request here:

Need Help With a Formula reposting my Data

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

www.mrexcel.com

...which can add to confusion and duplication of effort.

Here is my best guess for what you want, but you haven't answered questions I asked earlier that would address some of the uncertainty. Also note that the yellow cell (Prd-Barge) will never match anything in the lookup table (see my earlier questions about this)...for now, I have no matches return a KPI of 0 hours, so nothing is subtracted from the Actual Time Spent.

MrExcel_20220715b.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		ILSP	Deck Load Threshold	KPI (h)	No's of Visit	Total Hours	Average Hours
4	Rigs&Barges	JackRigs	>=70%	20	1	43	43
5	Rigs&Barges	JackRigs	<=69%	14	0	0	N/A
6	Complex	Complex	N/A	12	1	14.7666667	14.7666667
7	Drl-Island	Drl-Island	N/A	10	0	0	N/A
8	Prd-Island	Prd-Island	N/A	10	0	0	N/A
9		April-22
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 (d)	Exceed KPI (h)
12	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44678.2986	44678.3347	0.0361111		Apr-22	1044	ABK Complex	80%	Complex	1.11527778	14.766667
13	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44678.4028	44678.4083	0.0055556		May-22	1044	ABK Complex	80%	Complex	1.11527778	14.766667
14	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44679.2986	44679.3278	0.0291667		May-22	1044	Mutawa-202	0%		0.01041667	0.25
15	1044 ABK Complex	Apr-22	1044	Z-QUEEN	ABK Complex	80%	44679.3438	44679.3493	0.0055556		Apr-22	1045	Rig Vivekanand-3	100%	Rigs&Barges	2.625	43
16	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44682.2847	44682.2965	0.0118056		Apr-22	1045	Rig Yemillah	0%	Rigs&Barges	0.50694444	0
17	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44682.3507	44683.3563	1.0055556		Apr-22	1046	Nasr Complex	0%		0.42708333	10.25
18	1044 Mutawa-202	May-22	1044	Z-QUEEN	Mutawa-202	0%	44682.6042	44682.6146	0.0104167		Apr-22	1046	Barge Pepper	20%	Prd-Barge	0.10416667	2.5
19	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44683.3715	44683.3847	0.0131944		Apr-22	1047	Al Qatia Island	50%	Drl-Island	0.02777778	0
20	1044 ABK Complex	May-22	1044	Z-QUEEN	ABK Complex	80%	44683.4444	44683.4528	0.0083333		Jan-00	0	0	0%		0	0
21	1045 Rig Vivekanand-3	Apr-22	1045	QMS MARIMBA	Rig Vivekanand-3	100%	44678.6458	44678.8403	0.1944444
22	1045 Rig Yemillah	Apr-22	1045	QMS MARIMBA	Rig Yemillah	0%	44678.9306	44679.4375	0.5069444
23	1045 Rig Vivekanand-3	Apr-22	1045	QMS MARIMBA	Rig Vivekanand-3	100%	44679.5	44681.9306	2.4305556
24	1046 Nasr Complex	Apr-22	1046	ADNOC-810	Nasr Complex	0%	44678.3333	44678.7604	0.4270833
25	1046 Barge Pepper	Apr-22	1046	ADNOC-810	Barge Pepper	20%	44678.8958	44679	0.1041667
26	1047 Al Qatia Island	Apr-22	1047	LCT-WARDEH	Al Qatia Island	50%	44679.0833	44679.1111	0.0277778
Sheet9

Cell Formulas
Range	Formula
E4	E4	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$Q$12:$Q$900000,">0",$N$12:$N$900000,">=70%")
F4	F4	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A4,$N$12:$N$900000,">=70%")
G4:G8	G4	=IFERROR(F4/E4,"N/A")
E5	E5	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$Q$12:$Q$900000,">0",$N$12:$N$900000,"<=69%")
F5	F5	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A5,$N$12:$N$900000,"<=69%")
E6:E8	E6	=COUNTIFS($K$12:$K$900000,$B$9,$O$12:$O$900000,$A6,$Q$12:$Q$900000,">0")
F6:F8	F6	=SUMIFS($Q$12:$Q$900000,$K$12:$K$900000,$B$9,$O$12:$O$900000,$A6)
A12:A26	A12	=Vessel_Tracking[@Voyage]&" "&Vessel_Tracking[@Location]
B12:B26	B12	=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
K12:N20	K12	=UNIQUE(CHOOSE({1,2,3,4},B12:B2605,C12:C2605,E12:E2605,F12:F2605))
P12:P20	P12	=SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)
Q12:Q20	Q12	=IF(AND(O12="Rigs&Barges",N12>0.69,N12<0.7),"undefined",MAX(0,P12*24-IF(O12="Rigs&Barges",(N12>=0.7)*$D$4 + (N12<=0.69)*$D$5, IFERROR(INDEX($D$6:$D$8,MATCH(O12,$A$6:$A$8,0)),0))))
O14,O20:O26,O17	O14	=IFERROR(VLOOKUP(M14,Location,2,0),"")
I12:I26	I12	=[@[Location End Date & Time ]]-[@[Location Start Date & Time ]]
Dynamic array formulas.