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
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 |