Lookup with criteria and give actual sum base on two criteria

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
240
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Guys

well am trouble today as my boss ask me to update my data in a way to sum the actual of time spent for each each location as per voyage in other column

And i have created my data in this way that in Column L4 and M4 which i have giving a sample

So i need a solution that extract unique Rig Name in Column L4 and to give me the actual spent hours in column M4 , i will appreciate if i can get a solution to this trouble

Regards

If there is a formula to ease my stress i will be more glade with solution
Book2
BCDEFGHIJKLM
2Criteria 2Criteria 1
3Month Issued Month Voyage Vessel Name Deck Status Departed Rig Name Start Time End Time HoursRig Name Actual Hours
401-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC02/01/21 08:5502/01/21 12:153:20USSC7:25
501-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30Jopetwill-30002/01/21 13:3502/01/21 19:506:15Jopetwill-3006:15
601-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC02/01/21 20:2502/01/21 21:301:05
701-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC03/01/21 07:4503/01/21 10:453:00
801-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Al Ghallan Island 02/01/21 07:1002/01/21 12:185:08Al Ghallan Island 5:08
901-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Asseifiya Island 02/01/21 14:1802/01/21 15:000:42Asseifiya Island 0:42
1001-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Ettouk Island 02/01/21 17:4002/01/21 18:421:02Ettouk Island 1:02
1101-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Umm Al Anbar03/01/21 07:0003/01/21 10:423:42Umm Al Anbar3:42
1201-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Deep Driller-304/01/21 04:1804/01/21 16:4212:24Deep Driller-312:24
1301-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Artabhatt-129/12/20 08:1529/12/20 15:207:05Rig Artabhatt-121:35
1401-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Al Bzoom30/12/20 15:5030/12/20 21:005:10Rig Al Bzoom
1501-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Artabhatt-131/12/20 04:0031/12/20 18:3014:30
1601-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Rig Artabhatt-102/01/21 09:4502/01/21 10:551:10
1701-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Barge Pride 02/01/21 13:4002/01/21 16:302:50
1801-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Rig Diyina02/01/21 20:4503/01/21 02:405:55
1901-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Deep Driller-303/01/21 10:1503/01/21 12:101:55
2001-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Deep Driller-303/01/21 20:1004/01/21 04:007:50
2101-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Rig Hudairiyat02/01/21 16:1804/01/21 16:3048:12
2201-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Rig Hudairiyat04/01/21 21:0005/01/21 11:5414:54
2301-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Barge Shamal15/01/21 21:0016/01/21 01:064:06
2401-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Lulu01/01/21 23:5902/01/21 00:450:46
2501-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Hail02/01/21 11:2502/01/21 13:001:35
2601-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig VKN-302/01/21 22:0002/01/21 23:101:10
2701-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Ghallan03/01/21 08:3504/01/21 03:4519:10
2801-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig SMS Faith 03/01/21 06:0003/01/21 08:482:48
2901-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Al Gharbia03/01/21 12:0003/01/21 21:009:00
3001-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Marawwah04/01/21 00:3504/01/21 14:5014:15
3101-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Yemillah04/01/21 16:3005/01/21 00:017:31
3201-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Al Hail05/01/21 05:3005/01/21 20:0014:30
3301-Jan-21Jan-213711MAC PHOENIXHalf01/01/21 21:22Rig Muhaiyimat02/01/21 04:1302/01/21 15:4511:32
3401-Jan-21Jan-213711MAC PHOENIXHalf01/01/21 21:22Deep Driller-302/01/21 17:3003/01/21 12:1518:45
3501-Jan-21Jan-213712ADNOC-812Half01/01/21 21:15Barge Al Hyleh02/01/21 09:4502/01/21 18:108:25
3601-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge MB-102/01/21 10:4502/01/21 15:505:05
3701-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge Pesto03/01/21 08:3003/01/21 13:254:55
3801-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZWSC04/01/21 08:4004/01/21 09:200:40
3901-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZCSC04/01/21 13:4004/01/21 17:454:05
4001-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZCSC04/01/21 15:5304/01/21 17:451:52
4101-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge MB-104/01/21 20:0004/01/21 20:300:30
4201-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZWSC05/01/21 08:4005/01/21 11:453:05
4301-Jan-21Jan-211LCT-TARFFAHFull 03/01/21 07:10Zirku Island03/01/21 20:4004/01/21 08:5012:10
4401-Jan-21Jan-211LCT-TARFFAHFull 03/01/21 07:10Ettouk Island 04/01/21 17:4005/01/21 08:2514:45
4501-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT03/01/21 23:1504/01/21 01:252:10
4601-Jan-21Jan-212ADNOC-850Half03/01/21 13:15UZWSP04/01/21 02:5004/01/21 03:450:55
4701-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT04/01/21 08:3004/01/21 18:3010:00
4801-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT05/01/21 07:2505/01/21 11:504:25
4901-Jan-21Jan-212ADNOC-850Half03/01/21 13:15UZNSP05/01/21 13:0005/01/21 15:302:30
5001-Jan-21Jan-213ADNOC-510Full 02/01/21 14:20Rig Sms Mariam 03/01/21 07:2004/01/21 02:1518:55
5101-Jan-21Jan-214A-GRACEFull 02/01/21 06:15Rig Makasib03/01/21 05:0305/01/21 12:0855:05
5201-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC03/01/21 07:3003/01/21 14:206:50
5301-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC04/01/21 07:4004/01/21 17:309:50
5401-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35Barge Pesto04/01/21 18:0804/01/21 18:550:47
5501-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC05/01/21 07:5505/01/21 18:2510:30
5601-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Al Ghallan03/01/21 10:5003/01/21 11:250:35
5701-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Yemillah03/01/21 12:3003/01/21 14:001:30
5801-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Al Hail04/01/21 01:1004/01/21 17:3016:20
5901-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Makasib05/01/21 19:3006/01/21 07:4012:10
6001-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Qatia Island 03/01/21 19:2003/01/21 22:002:40
6101-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Bu Sikeen Island04/01/21 07:1004/01/21 10:002:50
6201-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Qatia Island 04/01/21 13:4004/01/21 22:308:50
6301-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Ghallan Island 05/01/21 08:4505/01/21 12:003:15
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:F63Cell Valuecontains "NAF"textNO
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
A pivot table may do what you need, and work instantly calculating the results you need

i can see how the Rig and time works not sure how the Voyage part meant to work exactly , as you are not showing that in the example

any way hers a pivot table
chemistry_v1.xlsx
ABCDEFGHIJKLMNOP
1Criteria 2Criteria 1
2Month Issued Month Voyage Vessel Name Deck Status Departed Rig Name Start Time End Time HoursRig Name Actual HoursRig Name Voyage Sum of Hours
34419744197.034723705ADNOC-810Half1/1/21 9:30USSC1/2/21 8:551/2/21 12:1503:20:00USSC07:25:00ACPT216:35:00
44419744197.034723705ADNOC-810Half1/1/21 9:30Jopetwill-3001/2/21 13:351/2/21 19:5006:15:00Jopetwill-30006:15:00Al Ghallan Island 703:15:00
54419744197.034723705ADNOC-810Half1/1/21 9:30USSC1/2/21 20:251/2/21 21:3001:05:00370605:08:00
64419744197.034723705ADNOC-810Half1/1/21 9:30USSC1/3/21 7:451/3/21 10:4503:00:00Al Qatia Island 711:30:00
74419744196.958333706ADNOC-224Half1/1/21 18:48Al Ghallan Island 1/2/21 7:101/2/21 12:1805:08:00Al Ghallan Island 05:08:00Asseifiya Island 370600:42:00
84419744196.958333706ADNOC-224Half1/1/21 18:48Asseifiya Island 1/2/21 14:181/2/21 15:0000:42:00Asseifiya Island 00:42:00Barge Al Hyleh371208:25:00
94419744196.958333706ADNOC-224Half1/1/21 18:48Ettouk Island 1/2/21 17:401/2/21 18:4201:02:00Ettouk Island 01:02:00Barge MB-1371305:35:00
104419744196.958333706ADNOC-224Half1/1/21 18:48Umm Al Anbar1/3/21 7:001/3/21 10:4203:42:00Umm Al Anbar03:42:00Barge Pesto500:47:00
114419744196.958333706ADNOC-224Half1/1/21 18:48Deep Driller-31/4/21 4:181/4/21 16:4212:24:00Deep Driller-312:24:00371304:55:00
124416644197.479173666-OASL SWIFTHalf1/2/21 16:00Rig Artabhatt-112/29/20 8:1512/29/20 15:2007:05:00Rig Artabhatt-121:35:00Barge Pride 370702:50:00
134416644197.479173666-OASL SWIFTHalf1/2/21 16:00Rig Al Bzoom12/30/20 15:5012/30/20 21:0005:10:00Rig Al BzoomBarge Shamal370804:06:00
144416644197.479173666-OASL SWIFTHalf1/2/21 16:00Rig Artabhatt-112/31/20 4:0012/31/20 18:3014:30:00Bu Sikeen Island702:50:00
154419744197.052083707Z-POWERHalf1/1/21 19:00Rig Artabhatt-11/2/21 9:451/2/21 10:5501:10:00Deep Driller-3370612:24:00
164419744197.052083707Z-POWERHalf1/1/21 19:00Barge Pride 1/2/21 13:401/2/21 16:3002:50:00370709:45:00
174419744197.052083707Z-POWERHalf1/1/21 19:00Rig Diyina1/2/21 20:451/3/21 2:4005:55:00371118:45:00
184419744197.052083707Z-POWERHalf1/1/21 19:00Deep Driller-31/3/21 10:151/3/21 12:1001:55:00Ettouk Island 114:45:00
194419744197.052083707Z-POWERHalf1/1/21 19:00Deep Driller-31/3/21 20:101/4/21 4:0007:50:00370601:02:00
204419744197.354173708SMIT LUZONFull 1/1/21 20:30Rig Hudairiyat1/2/21 16:181/4/21 16:3000:12:00Jopetwill-300370506:15:00
214419744197.354173708SMIT LUZONFull 1/1/21 20:30Rig Hudairiyat1/4/21 21:001/5/21 11:5414:54:00Rig Al Bzoom3666-O05:10:00
224419744197.354173708SMIT LUZONFull 1/1/21 20:30Barge Shamal1/15/21 21:001/16/21 1:0604:06:00Rig Al Ghallan600:35:00
234419744197.222223709SMIT LUMUTHalf1/1/21 19:15Rig Al Lulu1/1/21 23:591/2/21 0:4500:46:00370919:10:00
244419744197.222223709SMIT LUMUTHalf1/1/21 19:15Rig Al Hail1/2/21 11:251/2/21 13:0001:35:00Rig Al Gharbia371009:00:00
254419744197.222223709SMIT LUMUTHalf1/1/21 19:15Rig VKN-31/2/21 22:001/2/21 23:1001:10:00Rig Al Hail616:20:00
264419744197.222223709SMIT LUMUTHalf1/1/21 19:15Rig Al Ghallan1/3/21 8:351/4/21 3:4519:10:00370901:35:00
274419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig SMS Faith 1/3/21 6:001/3/21 8:4802:48:00371014:30:00
284419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig Al Gharbia1/3/21 12:001/3/21 21:0009:00:00Rig Al Lulu370900:46:00
294419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig Marawwah1/4/21 0:351/4/21 14:5014:15:00Rig Artabhatt-1370701:10:00
304419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig Yemillah1/4/21 16:301/5/21 0:0107:31:003666-O21:35:00
314419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig Al Hail1/5/21 5:301/5/21 20:0014:30:00Rig Diyina370705:55:00
324419744197.406253711MAC PHOENIXHalf1/1/21 21:22Rig Muhaiyimat1/2/21 4:131/2/21 15:4511:32:00Rig Hudairiyat370815:06:00
334419744197.406253711MAC PHOENIXHalf1/1/21 21:22Deep Driller-31/2/21 17:301/3/21 12:1518:45:00Rig Makasib407:05:00
344419744197.336813712ADNOC-812Half1/1/21 21:15Barge Al Hyleh1/2/21 9:451/2/21 18:1008:25:00612:10:00
354419744197.576393713A-HERCULESHalf1/2/21 0:30Barge MB-11/2/21 10:451/2/21 15:5005:05:00Rig Marawwah371014:15:00
364419744197.576393713A-HERCULESHalf1/2/21 0:30Barge Pesto1/3/21 8:301/3/21 13:2504:55:00Rig Muhaiyimat371111:32:00
374419744197.576393713A-HERCULESHalf1/2/21 0:30ZWSC1/4/21 8:401/4/21 9:2000:40:00Rig SMS Faith 371002:48:00
384419744197.576393713A-HERCULESHalf1/2/21 0:30ZCSC1/4/21 13:401/4/21 17:4504:05:00Rig Sms Mariam 318:55:00
394419744197.576393713A-HERCULESHalf1/2/21 0:30ZCSC1/4/21 15:531/4/21 17:4501:52:00Rig VKN-3370901:10:00
404419744197.576393713A-HERCULESHalf1/2/21 0:30Barge MB-11/4/21 20:001/4/21 20:3000:30:00Rig Yemillah601:30:00
414419744197.576393713A-HERCULESHalf1/2/21 0:30ZWSC1/5/21 8:401/5/21 11:4503:05:00371007:31:00
424419744198.666671LCT-TARFFAHFull 1/3/21 7:10Zirku Island1/3/21 20:401/4/21 8:5012:10:00Umm Al Anbar370603:42:00
434419744198.666671LCT-TARFFAHFull 1/3/21 7:10Ettouk Island 1/4/21 17:401/5/21 8:2514:45:00USSC370507:25:00
444419744199.010422ADNOC-850Half1/3/21 13:15ACPT1/3/21 23:151/4/21 1:2502:10:00UZNSP202:30:00
454419744199.010422ADNOC-850Half1/3/21 13:15UZWSP1/4/21 2:501/4/21 3:4500:55:00UZWSP200:55:00
464419744199.010422ADNOC-850Half1/3/21 13:15ACPT1/4/21 8:301/4/21 18:3010:00:00ZCSC371305:57:00
474419744199.010422ADNOC-850Half1/3/21 13:15ACPT1/5/21 7:251/5/21 11:5004:25:00Zirku Island112:10:00
484419744199.010422ADNOC-850Half1/3/21 13:15UZNSP1/5/21 13:001/5/21 15:3002:30:00ZWSC503:10:00
4944197441983ADNOC-510Full 1/2/21 14:20Rig Sms Mariam 1/3/21 7:201/4/21 2:1518:55:00371303:45:00
504419744197.902784A-GRACEFull 1/2/21 6:15Rig Makasib1/3/21 5:031/5/21 12:0807:05:00
514419744198.354175MARCAP-2Full 1/2/21 17:35ZWSC1/3/21 7:301/3/21 14:2006:50:00
524419744198.354175MARCAP-2Full 1/2/21 17:35ZWSC1/4/21 7:401/4/21 17:3009:50:00
534419744198.354175MARCAP-2Full 1/2/21 17:35Barge Pesto1/4/21 18:081/4/21 18:5500:47:00
544419744198.354175MARCAP-2Full 1/2/21 17:35ZWSC1/5/21 7:551/5/21 18:2510:30:00
554419744198.041676QMS DELTAHalf1/2/21 21:50Rig Al Ghallan1/3/21 10:501/3/21 11:2500:35:00
564419744198.041676QMS DELTAHalf1/2/21 21:50Rig Yemillah1/3/21 12:301/3/21 14:0001:30:00
574419744198.041676QMS DELTAHalf1/2/21 21:50Rig Al Hail1/4/21 1:101/4/21 17:3016:20:00
584419744198.041676QMS DELTAHalf1/2/21 21:50Rig Makasib1/5/21 19:301/6/21 7:4012:10:00
594419744198.434037ADNOC-1011Half1/3/21 3:40Al Qatia Island 1/3/21 19:201/3/21 22:0002:40:00
604419744198.434037ADNOC-1011Half1/3/21 3:40Bu Sikeen Island1/4/21 7:101/4/21 10:0002:50:00
614419744198.434037ADNOC-1011Half1/3/21 3:40Al Qatia Island 1/4/21 13:401/4/21 22:3008:50:00
624419744198.434037ADNOC-1011Half1/3/21 3:40Al Ghallan Island 1/5/21 8:451/5/21 12:0003:15:00
Sheet11
 
Upvote 0
Hi Etaf

Thanks so much, based on operation the voyage is given to a Vessel on trips, and when vessel return back it will be issued another voyage this process continue for every vessel and on same voyage vessel might go to different rigs for cargo operation so my idea is extract the unique Rig name L4 base on the voyage then give me the sum in M4

if you can see i have provide a sample of what am trying to achieve my trouble is to extract the unique name base on the voyage Auto with a formula same i have use sumifs to sum the actual time rigs has work.

though am unable t explain much but i believe the data will be okay with what i required to do

Like i said my main goal is to sum the actual time spent for each rigs on a voyage if you can see in column D you will notice that i voyage repeats even when the vessel visit the rigs twice or more so in column L4 i have i will regard USSC as one to give me all total Hours Spent for the voyage trips

Appreciate your support

Regards

Book21111.xlsx
BCDEFGHIJKLM
2Criteria 2Criteria 1
3Month Issued Month Voyage NoVessel Name Deck Status Departed Rig Name Start Time End Time HoursUnique Rig Name per voyage No.Actual Spent Hours on visit Voyage to Rig
401-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC02/01/21 08:5502/01/21 12:153:20USSC7:25
501-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30Jopetwill-30002/01/21 13:3502/01/21 19:506:15Jopetwill-3006:15
601-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC02/01/21 20:2502/01/21 21:301:050:00
701-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC03/01/21 07:4503/01/21 10:453:000:00
801-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Al Ghallan Island 02/01/21 07:1002/01/21 12:185:08Al Ghallan Island 5:08
901-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Asseifiya Island 02/01/21 14:1802/01/21 15:000:42Asseifiya Island 0:42
1001-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Ettouk Island 02/01/21 17:4002/01/21 18:421:02Ettouk Island 1:02
1101-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Umm Al Anbar03/01/21 07:0003/01/21 10:423:42Umm Al Anbar3:42
1201-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Deep Driller-304/01/21 04:1804/01/21 16:4212:24Deep Driller-312:24
1301-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Artabhatt-129/12/20 08:1529/12/20 15:207:05Rig Artabhatt-121:35
1401-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Al Bzoom30/12/20 15:5030/12/20 21:005:10Rig Al Bzoom5:10
1501-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Artabhatt-131/12/20 04:0031/12/20 18:3014:300:00
1601-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Rig Artabhatt-102/01/21 09:4502/01/21 10:551:10Rig Artabhatt-11:10
1701-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Barge Pride 02/01/21 13:4002/01/21 16:302:50Barge Pride 2:50
1801-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Rig Diyina02/01/21 20:4503/01/21 02:405:55Rig Diyina5:55
1901-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Deep Driller-303/01/21 10:1503/01/21 12:101:55Deep Driller-39:45
2001-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Deep Driller-303/01/21 20:1004/01/21 04:007:500:00
2101-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Rig Hudairiyat02/01/21 16:1804/01/21 16:3048:12Rig Hudairiyat63:06
2201-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Rig Hudairiyat04/01/21 21:0005/01/21 11:5414:54Barge Shamal4:06
2301-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Barge Shamal15/01/21 21:0016/01/21 01:064:06Rig Al Lulu0:00
2401-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Lulu01/01/21 23:5902/01/21 00:450:46Rig Al Hail1:35
2501-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Hail02/01/21 11:2502/01/21 13:001:35Rig VKN-31:10
2601-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig VKN-302/01/21 22:0002/01/21 23:101:10Rig Al Ghallan19:10
2701-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Ghallan03/01/21 08:3504/01/21 03:4519:100:00
2801-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig SMS Faith 03/01/21 06:0003/01/21 08:482:48Rig SMS Faith 2:48
2901-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Al Gharbia03/01/21 12:0003/01/21 21:009:00Rig Al Gharbia9:00
3001-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Marawwah04/01/21 00:3504/01/21 14:5014:15Rig Marawwah14:15
3101-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Yemillah04/01/21 16:3005/01/21 00:017:31Rig Yemillah7:31
3201-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Al Hail05/01/21 05:3005/01/21 20:0014:30Rig Al Hail14:30
3301-Jan-21Jan-213711MAC PHOENIXHalf01/01/21 21:22Rig Muhaiyimat02/01/21 04:1302/01/21 15:4511:32Rig Muhaiyimat11:32
3401-Jan-21Jan-213711MAC PHOENIXHalf01/01/21 21:22Deep Driller-302/01/21 17:3003/01/21 12:1518:45Deep Driller-318:45
3501-Jan-21Jan-213712ADNOC-812Half01/01/21 21:15Barge Al Hyleh02/01/21 09:4502/01/21 18:108:25Barge Al Hyleh8:25
3601-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge MB-102/01/21 10:4502/01/21 15:505:05Barge MB-15:35
3701-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge Pesto03/01/21 08:3003/01/21 13:254:55Barge Pesto4:55
3801-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZWSC04/01/21 08:4004/01/21 09:200:40ZWSC3:45
3901-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZCSC04/01/21 13:4004/01/21 17:454:05ZCSC5:57
4001-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZCSC04/01/21 15:5304/01/21 17:451:520:00
4101-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge MB-104/01/21 20:0004/01/21 20:300:300:00
4201-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZWSC05/01/21 08:4005/01/21 11:453:050:00
4301-Jan-21Jan-211LCT-TARFFAHFull 03/01/21 07:10Zirku Island03/01/21 20:4004/01/21 08:5012:10Zirku Island12:10
4401-Jan-21Jan-211LCT-TARFFAHFull 03/01/21 07:10Ettouk Island 04/01/21 17:4005/01/21 08:2514:45Ettouk Island 14:45
4501-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT03/01/21 23:1504/01/21 01:252:10ACPT16:35
4601-Jan-21Jan-212ADNOC-850Half03/01/21 13:15UZWSP04/01/21 02:5004/01/21 03:450:55UZWSP0:55
4701-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT04/01/21 08:3004/01/21 18:3010:00UZNSP2:30
4801-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT05/01/21 07:2505/01/21 11:504:250:00
4901-Jan-21Jan-212ADNOC-850Half03/01/21 13:15UZNSP05/01/21 13:0005/01/21 15:302:300:00
5001-Jan-21Jan-213ADNOC-510Full 02/01/21 14:20Rig Sms Mariam 03/01/21 07:2004/01/21 02:1518:55Rig Sms Mariam 18:55
5101-Jan-21Jan-214A-GRACEFull 02/01/21 06:15Rig Makasib03/01/21 05:0305/01/21 12:0855:05Rig Makasib55:05
5201-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC03/01/21 07:3003/01/21 14:206:50ZWSC27:10
5301-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC04/01/21 07:4004/01/21 17:309:50Barge Pesto0:47
5401-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35Barge Pesto04/01/21 18:0804/01/21 18:550:470:00
5501-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC05/01/21 07:5505/01/21 18:2510:300:00
5601-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Al Ghallan03/01/21 10:5003/01/21 11:250:35Rig Al Ghallan0:35
5701-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Yemillah03/01/21 12:3003/01/21 14:001:30Rig Yemillah1:30
5801-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Al Hail04/01/21 01:1004/01/21 17:3016:20Rig Al Hail16:20
5901-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Makasib05/01/21 19:3006/01/21 07:4012:10Rig Makasib12:10
6001-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Qatia Island 03/01/21 19:2003/01/21 22:002:40Al Qatia Island 11:30
6101-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Bu Sikeen Island04/01/21 07:1004/01/21 10:002:50Bu Sikeen Island2:50
6201-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Qatia Island 04/01/21 13:4004/01/21 22:308:50Al Ghallan Island 3:15
6301-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Ghallan Island 05/01/21 08:4505/01/21 12:003:150:00
6401-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20Barge Pesto05/01/21 08:0005/01/21 14:154:15Barge Pesto4:15
6501-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20ZWSC05/01/21 14:3005/01/21 15:305:15ZWSC5:15
6601-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20ACPT05/01/21 17:0005/01/21 18:406:15ACPT6:15
6701-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20ZCSC06/01/21 07:3506/01/21 11:207:15ZCSC7:15
6801-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20ZWSC06/01/21 14:0006/01/21 16:15
Sheet1
Cell Formulas
RangeFormula
M4:M67M4=IFERROR(SUMIFS($K$4:$K$68,$H$4:$H$68,L4,$D$4:$D$68,D4),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F68Cell Valuecontains "NAF"textNO
F64:F67Cell Valuecontains "NAF"textNO
F4:F63Cell Valuecontains "NAF"textNO
 
Upvote 0
Although the results are in a different order, they are giving you the Time you want by Rig & Voyage.
I not around today , so will have to look at using formula's to layout in the way you want.
I suspect a combination of IF, COUNTIFS() & SUMIFS should work
 
Upvote 0
in fact having written the above , i then thought it may be easier with the countifs etc

The COUNTIFS() range starts at the first cell and is locked into that range
COUNTIFS(G3:$G$3,G3,C3:$C$3,C3)
So G3 is fixed with $ and then as you copy down expands $G$3:G3 , $G$3:G4, $G$3:G5 etc, and only include RIG if the RIG & VOYAGE only appears once , hence count =1 , that will leave the RIG cell blank if it already exists above for that RIG & VOYAGE

the Time is formatted as [H]:MM to show hours greater than 24

chemistry_v1.xlsx
ABCDEFGHIJKL
1Criteria 2Criteria 1
2Month Issued Month Voyage Vessel Name Deck Status Departed Rig Name Start Time End Time HoursRig Name Actual Hours
34419744197.034723705ADNOC-810Half1/1/21 9:30USSC1/2/21 8:551/2/21 12:1503:20:00USSC7:25
44419744197.034723705ADNOC-810Half1/1/21 9:30Jopetwill-3001/2/21 13:351/2/21 19:5006:15:00Jopetwill-3006:15
54419744197.034723705ADNOC-810Half1/1/21 9:30USSC1/2/21 20:251/2/21 21:3001:05:00  
64419744197.034723705ADNOC-810Half1/1/21 9:30USSC1/3/21 7:451/3/21 10:4503:00:00  
74419744196.958333706ADNOC-224Half1/1/21 18:48Al Ghallan Island 1/2/21 7:101/2/21 12:1805:08:00Al Ghallan Island 5:08
84419744196.958333706ADNOC-224Half1/1/21 18:48Asseifiya Island 1/2/21 14:181/2/21 15:0000:42:00Asseifiya Island 0:42
94419744196.958333706ADNOC-224Half1/1/21 18:48Ettouk Island 1/2/21 17:401/2/21 18:4201:02:00Ettouk Island 1:02
104419744196.958333706ADNOC-224Half1/1/21 18:48Umm Al Anbar1/3/21 7:001/3/21 10:4203:42:00Umm Al Anbar3:42
114419744196.958333706ADNOC-224Half1/1/21 18:48Deep Driller-31/4/21 4:181/4/21 16:4212:24:00Deep Driller-312:24
124416644197.479173666-OASL SWIFTHalf1/2/21 16:00Rig Artabhatt-112/29/20 8:1512/29/20 15:2007:05:00Rig Artabhatt-121:35
134416644197.479173666-OASL SWIFTHalf1/2/21 16:00Rig Al Bzoom12/30/20 15:5012/30/20 21:0005:10:00Rig Al Bzoom5:10
144416644197.479173666-OASL SWIFTHalf1/2/21 16:00Rig Artabhatt-112/31/20 4:0012/31/20 18:3014:30:00  
154419744197.052083707Z-POWERHalf1/1/21 19:00Rig Artabhatt-11/2/21 9:451/2/21 10:5501:10:00Rig Artabhatt-11:10
164419744197.052083707Z-POWERHalf1/1/21 19:00Barge Pride 1/2/21 13:401/2/21 16:3002:50:00Barge Pride 2:50
174419744197.052083707Z-POWERHalf1/1/21 19:00Rig Diyina1/2/21 20:451/3/21 2:4005:55:00Rig Diyina5:55
184419744197.052083707Z-POWERHalf1/1/21 19:00Deep Driller-31/3/21 10:151/3/21 12:1001:55:00Deep Driller-39:45
194419744197.052083707Z-POWERHalf1/1/21 19:00Deep Driller-31/3/21 20:101/4/21 4:0007:50:00  
204419744197.354173708SMIT LUZONFull 1/1/21 20:30Rig Hudairiyat1/2/21 16:181/4/21 16:3000:12:00Rig Hudairiyat63:06
214419744197.354173708SMIT LUZONFull 1/1/21 20:30Rig Hudairiyat1/4/21 21:001/5/21 11:5414:54:00  
224419744197.354173708SMIT LUZONFull 1/1/21 20:30Barge Shamal1/15/21 21:001/16/21 1:0604:06:00Barge Shamal4:06
234419744197.222223709SMIT LUMUTHalf1/1/21 19:15Rig Al Lulu1/1/21 23:591/2/21 0:4500:46:00Rig Al Lulu0:46
244419744197.222223709SMIT LUMUTHalf1/1/21 19:15Rig Al Hail1/2/21 11:251/2/21 13:0001:35:00Rig Al Hail1:35
254419744197.222223709SMIT LUMUTHalf1/1/21 19:15Rig VKN-31/2/21 22:001/2/21 23:1001:10:00Rig VKN-31:10
264419744197.222223709SMIT LUMUTHalf1/1/21 19:15Rig Al Ghallan1/3/21 8:351/4/21 3:4519:10:00Rig Al Ghallan19:10
274419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig SMS Faith 1/3/21 6:001/3/21 8:4802:48:00Rig SMS Faith 2:48
284419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig Al Gharbia1/3/21 12:001/3/21 21:0009:00:00Rig Al Gharbia9:00
294419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig Marawwah1/4/21 0:351/4/21 14:5014:15:00Rig Marawwah14:15
304419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig Yemillah1/4/21 16:301/5/21 0:0107:31:00Rig Yemillah7:31
314419744197.631253710B-LIBERTY-313NAF1/2/21 12:45Rig Al Hail1/5/21 5:301/5/21 20:0014:30:00Rig Al Hail14:30
324419744197.406253711MAC PHOENIXHalf1/1/21 21:22Rig Muhaiyimat1/2/21 4:131/2/21 15:4511:32:00Rig Muhaiyimat11:32
334419744197.406253711MAC PHOENIXHalf1/1/21 21:22Deep Driller-31/2/21 17:301/3/21 12:1518:45:00Deep Driller-318:45
344419744197.336813712ADNOC-812Half1/1/21 21:15Barge Al Hyleh1/2/21 9:451/2/21 18:1008:25:00Barge Al Hyleh8:25
354419744197.576393713A-HERCULESHalf1/2/21 0:30Barge MB-11/2/21 10:451/2/21 15:5005:05:00Barge MB-15:35
364419744197.576393713A-HERCULESHalf1/2/21 0:30Barge Pesto1/3/21 8:301/3/21 13:2504:55:00Barge Pesto4:55
374419744197.576393713A-HERCULESHalf1/2/21 0:30ZWSC1/4/21 8:401/4/21 9:2000:40:00ZWSC3:45
384419744197.576393713A-HERCULESHalf1/2/21 0:30ZCSC1/4/21 13:401/4/21 17:4504:05:00ZCSC5:57
394419744197.576393713A-HERCULESHalf1/2/21 0:30ZCSC1/4/21 15:531/4/21 17:4501:52:00  
404419744197.576393713A-HERCULESHalf1/2/21 0:30Barge MB-11/4/21 20:001/4/21 20:3000:30:00  
414419744197.576393713A-HERCULESHalf1/2/21 0:30ZWSC1/5/21 8:401/5/21 11:4503:05:00  
424419744198.666671LCT-TARFFAHFull 1/3/21 7:10Zirku Island1/3/21 20:401/4/21 8:5012:10:00Zirku Island12:10
434419744198.666671LCT-TARFFAHFull 1/3/21 7:10Ettouk Island 1/4/21 17:401/5/21 8:2514:45:00Ettouk Island 14:45
444419744199.010422ADNOC-850Half1/3/21 13:15ACPT1/3/21 23:151/4/21 1:2502:10:00ACPT16:35
454419744199.010422ADNOC-850Half1/3/21 13:15UZWSP1/4/21 2:501/4/21 3:4500:55:00UZWSP0:55
464419744199.010422ADNOC-850Half1/3/21 13:15ACPT1/4/21 8:301/4/21 18:3010:00:00  
474419744199.010422ADNOC-850Half1/3/21 13:15ACPT1/5/21 7:251/5/21 11:5004:25:00  
484419744199.010422ADNOC-850Half1/3/21 13:15UZNSP1/5/21 13:001/5/21 15:3002:30:00UZNSP2:30
4944197441983ADNOC-510Full 1/2/21 14:20Rig Sms Mariam 1/3/21 7:201/4/21 2:1518:55:00Rig Sms Mariam 18:55
504419744197.902784A-GRACEFull 1/2/21 6:15Rig Makasib1/3/21 5:031/5/21 12:0807:05:00Rig Makasib55:05
514419744198.354175MARCAP-2Full 1/2/21 17:35ZWSC1/3/21 7:301/3/21 14:2006:50:00ZWSC27:10
524419744198.354175MARCAP-2Full 1/2/21 17:35ZWSC1/4/21 7:401/4/21 17:3009:50:00  
534419744198.354175MARCAP-2Full 1/2/21 17:35Barge Pesto1/4/21 18:081/4/21 18:5500:47:00Barge Pesto0:47
544419744198.354175MARCAP-2Full 1/2/21 17:35ZWSC1/5/21 7:551/5/21 18:2510:30:00  
554419744198.041676QMS DELTAHalf1/2/21 21:50Rig Al Ghallan1/3/21 10:501/3/21 11:2500:35:00Rig Al Ghallan0:35
564419744198.041676QMS DELTAHalf1/2/21 21:50Rig Yemillah1/3/21 12:301/3/21 14:0001:30:00Rig Yemillah1:30
574419744198.041676QMS DELTAHalf1/2/21 21:50Rig Al Hail1/4/21 1:101/4/21 17:3016:20:00Rig Al Hail16:20
584419744198.041676QMS DELTAHalf1/2/21 21:50Rig Makasib1/5/21 19:301/6/21 7:4012:10:00Rig Makasib12:10
594419744198.434037ADNOC-1011Half1/3/21 3:40Al Qatia Island 1/3/21 19:201/3/21 22:0002:40:00Al Qatia Island 11:30
604419744198.434037ADNOC-1011Half1/3/21 3:40Bu Sikeen Island1/4/21 7:101/4/21 10:0002:50:00Bu Sikeen Island2:50
614419744198.434037ADNOC-1011Half1/3/21 3:40Al Qatia Island 1/4/21 13:401/4/21 22:3008:50:00  
624419744198.434037ADNOC-1011Half1/3/21 3:40Al Ghallan Island 1/5/21 8:451/5/21 12:0003:15:00Al Ghallan Island 3:15
Sheet11
Cell Formulas
RangeFormula
K3K3=IF(COUNTIFS(G3:$G$3,G3,C3:$C$3,C3)=1,G3,"")
L3:L62L3=IF(K3="","",SUMIFS($J$3:$J$62,$G$3:$G$62,G3,$C$3:$C$62,C3))
K4:K62K4=IF(COUNTIFS(G$3:$G4,G4,C$3:$C4,C4)=1,G4,"")
 
Last edited:
Upvote 0
Solution
Hi etaf.

It so awesome :eek:seeing how amazing formula makes work easy in fact it just as i needed it, and it run so fast and make my task easy to extract:biggrin: am so so so happy, am happy joining the forum and learning more daily on excel.am so grateful..

well i have one more question which am ask to do after achieving the actual Time in column L4 now i created another column M4 to determine the KPI goals

Base on the KPI set is on Full & Half Deck so i need a formula that can give me the result of average time spent, that do not meet the KPI target only from column L3 based on Criteria in Column E.

The KPI set for average time spent on Full Deck cargo is 16:00
the KP set for average time spent on Half Deck cargo is 10:00

Presently I need to summarize the average monthly based with above KPI in Column K1 and K2 based criteria in yellow highlighted

Book21111.xlsx
BCDEFGHIJKLM
1Month01-Jan-21KPI16:00Average time spent on Full Deck Full
2KPI10:00Average time spent on Half Deck Half
3Month Issued Month Voyage NoVessel Name Deck Status Departed Rig Name Start Time End Time HoursUnique Rig Name per voyage No.Actual Spent Hours on visit Voyage to Rig
401-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC02/01/21 08:5502/01/21 12:153:20USSC7:25
501-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30Jopetwill-30002/01/21 13:3502/01/21 19:506:15Jopetwill-3006:15
601-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC02/01/21 20:2502/01/21 21:301:05 0:00
701-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC03/01/21 07:4503/01/21 10:453:00 0:00
801-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Al Ghallan Island 02/01/21 07:1002/01/21 12:185:08Al Ghallan Island 5:08
901-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Asseifiya Island 02/01/21 14:1802/01/21 15:000:42Asseifiya Island 0:42
1001-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Ettouk Island 02/01/21 17:4002/01/21 18:421:02Ettouk Island 1:02
1101-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Umm Al Anbar03/01/21 07:0003/01/21 10:423:42Umm Al Anbar3:42
1201-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Deep Driller-304/01/21 04:1804/01/21 16:4212:24Deep Driller-312:24
1301-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Artabhatt-129/12/20 08:1529/12/20 15:207:05Rig Artabhatt-121:35
1401-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Al Bzoom30/12/20 15:5030/12/20 21:005:10Rig Al Bzoom5:10
1501-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Artabhatt-131/12/20 04:0031/12/20 18:3014:300:00
1601-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Rig Artabhatt-102/01/21 09:4502/01/21 10:551:10Rig Artabhatt-11:10
1701-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Barge Pride 02/01/21 13:4002/01/21 16:302:50Barge Pride 2:50
1801-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Rig Diyina02/01/21 20:4503/01/21 02:405:55Rig Diyina5:55
1901-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Deep Driller-303/01/21 10:1503/01/21 12:101:55Deep Driller-39:45
2001-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Deep Driller-303/01/21 20:1004/01/21 04:007:500:00
2101-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Rig Hudairiyat02/01/21 16:1804/01/21 16:3048:12Rig Hudairiyat63:06
2201-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Rig Hudairiyat04/01/21 21:0005/01/21 11:5414:54Barge Shamal4:06
2301-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Barge Shamal15/01/21 21:0016/01/21 01:064:06Rig Al Lulu0:00
2401-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Lulu01/01/21 23:5902/01/21 00:450:46Rig Al Hail1:35
2501-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Hail02/01/21 11:2502/01/21 13:001:35Rig VKN-31:10
2601-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig VKN-302/01/21 22:0002/01/21 23:101:10Rig Al Ghallan19:10
2701-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Ghallan03/01/21 08:3504/01/21 03:4519:100:00
2801-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig SMS Faith 03/01/21 06:0003/01/21 08:482:48Rig SMS Faith 2:48
2901-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Al Gharbia03/01/21 12:0003/01/21 21:009:00Rig Al Gharbia9:00
3001-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Marawwah04/01/21 00:3504/01/21 14:5014:15Rig Marawwah14:15
3101-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Yemillah04/01/21 16:3005/01/21 00:017:31Rig Yemillah7:31
3201-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Al Hail05/01/21 05:3005/01/21 20:0014:30Rig Al Hail14:30
3301-Jan-21Jan-213711MAC PHOENIXHalf01/01/21 21:22Rig Muhaiyimat02/01/21 04:1302/01/21 15:4511:32Rig Muhaiyimat11:32
3401-Jan-21Jan-213711MAC PHOENIXHalf01/01/21 21:22Deep Driller-302/01/21 17:3003/01/21 12:1518:45Deep Driller-318:45
3501-Jan-21Jan-213712ADNOC-812Half01/01/21 21:15Barge Al Hyleh02/01/21 09:4502/01/21 18:108:25Barge Al Hyleh8:25
3601-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge MB-102/01/21 10:4502/01/21 15:505:05Barge MB-15:35
3701-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge Pesto03/01/21 08:3003/01/21 13:254:55Barge Pesto4:55
3801-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZWSC04/01/21 08:4004/01/21 09:200:40ZWSC3:45
3901-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZCSC04/01/21 13:4004/01/21 17:454:05ZCSC5:57
4001-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZCSC04/01/21 15:5304/01/21 17:451:520:00
4101-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge MB-104/01/21 20:0004/01/21 20:300:300:00
4201-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZWSC05/01/21 08:4005/01/21 11:453:050:00
4301-Jan-21Jan-211LCT-TARFFAHFull 03/01/21 07:10Zirku Island03/01/21 20:4004/01/21 08:5012:10Zirku Island12:10
4401-Jan-21Jan-211LCT-TARFFAHFull 03/01/21 07:10Ettouk Island 04/01/21 17:4005/01/21 08:2514:45Ettouk Island 14:45
4501-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT03/01/21 23:1504/01/21 01:252:10ACPT16:35
4601-Jan-21Jan-212ADNOC-850Half03/01/21 13:15UZWSP04/01/21 02:5004/01/21 03:450:55UZWSP0:55
4701-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT04/01/21 08:3004/01/21 18:3010:00UZNSP2:30
4801-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT05/01/21 07:2505/01/21 11:504:250:00
4901-Jan-21Jan-212ADNOC-850Half03/01/21 13:15UZNSP05/01/21 13:0005/01/21 15:302:300:00
5001-Jan-21Jan-213ADNOC-510Full 02/01/21 14:20Rig Sms Mariam 03/01/21 07:2004/01/21 02:1518:55Rig Sms Mariam 18:55
5101-Jan-21Jan-214A-GRACEFull 02/01/21 06:15Rig Makasib03/01/21 05:0305/01/21 12:0855:05Rig Makasib55:05
5201-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC03/01/21 07:3003/01/21 14:206:50ZWSC27:10
5301-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC04/01/21 07:4004/01/21 17:309:50Barge Pesto0:47
5401-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35Barge Pesto04/01/21 18:0804/01/21 18:550:470:00
5501-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC05/01/21 07:5505/01/21 18:2510:300:00
5601-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Al Ghallan03/01/21 10:5003/01/21 11:250:35Rig Al Ghallan0:35
5701-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Yemillah03/01/21 12:3003/01/21 14:001:30Rig Yemillah1:30
5801-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Al Hail04/01/21 01:1004/01/21 17:3016:20Rig Al Hail16:20
5901-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Makasib05/01/21 19:3006/01/21 07:4012:10Rig Makasib12:10
6001-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Qatia Island 03/01/21 19:2003/01/21 22:002:40Al Qatia Island 11:30
6101-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Bu Sikeen Island04/01/21 07:1004/01/21 10:002:50Bu Sikeen Island2:50
6201-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Qatia Island 04/01/21 13:4004/01/21 22:308:50Al Ghallan Island 3:15
6301-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Ghallan Island 05/01/21 08:4505/01/21 12:003:150:00
6401-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20Barge Pesto05/01/21 08:0005/01/21 14:154:15Barge Pesto4:15
6501-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20ZWSC05/01/21 14:3005/01/21 15:305:15ZWSC5:15
6601-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20ACPT05/01/21 17:0005/01/21 18:406:15ACPT6:15
6701-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20ZCSC06/01/21 07:3506/01/21 11:207:15ZCSC7:15
6801-Jan-21Jan-2124ADNOC-810Half04/01/21 16:20ZWSC06/01/21 14:0006/01/21 16:15
Sheet1
Cell Formulas
RangeFormula
L4L4=IF(COUNTIFS(H4:$H$4,H4,D4:$D$4,D4)=1,H4,"")
L5:L7L5=IF(COUNTIFS(H$4:$H5,H5,D$4:$D5,D5)=1,H5,"")
M4M4=IF(L4="","",SUMIFS($K$4:$K$63,$H$4:$H$63,H4,$D$4:$D$63,D4))
M5:M67M5=IFERROR(SUMIFS($K$4:$K$68,$H$4:$H$68,L5,$D$4:$D$68,D5),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F68Cell Valuecontains "NAF"textNO
F64:F67Cell Valuecontains "NAF"textNO
F4:F63Cell Valuecontains "NAF"textNO
 
Upvote 0
not sure i follow
Base on the KPI set is on Full & Half Deck so i need a formula that can give me the result of average time spent, that do not meet the KPI target only from column L3 based on Criteria in Column E.

The KPI set for average time spent on Full Deck cargo is 16:00
the KP set for average time spent on Half Deck cargo is 10:00
I dont know if this is a Target
or what average you are after ?
can you explain with examples

The average for all the times , FULL & HALF is
VARIOUS Sheets.xlsx
OPQR
1The KPI set for average time spent on Full Deck cargo is 16:00
2the KP set for average time spent on Half Deck cargo is 10:00
3
4Average?Target
5
6Full17:4916:00
7Half5:1810:00
8
9
Sheet13
Cell Formulas
RangeFormula
P6P6=AVERAGEIF(F:F,"full",M:M)
P7P7=AVERAGEIF(F:F,"half",M:M)
 
Upvote 0
Hi Etaf

Thanks for your feedback and support the average am looking for is for two KPI for every month

1. the average hours exceeding the 16hrs. KPI target on Full, for example you will see for full remarks in column F4, so I need to find the average in Column M4 that exceed the KPI target base on criteria monthly and same goes for Half 10Hrs KPI

my aim to to find the average exceeding the target KPI monthly for both 16hrs on Full and 10Hrs on Half.

I hope this explanations is ok

Regards
 
Upvote 0
really sorry not getting what the actual formula needs to be.

find the average exceeding the target KPI monthly for both 16hrs on Full and 10Hrs on Half.
Exceeding is the bit i'm struggling wiith and then average what ?

Row 12 is the first value in M (12.24 ) to exceed the Half KPI of 10 , that exceeds by 2.24 and then the average would be 2.24
Row 15 14.3 half , exceeds by 4.3 - so average of those 2 is 2.24+4.3 / 2
Row 21 63.06 FULL Target 16 , so exceeds target by 47.06 , then its average would be 47.06

I think i need in words some examples , so i can work out what you need
 
Upvote 0
Hi Etaf

I posted few table and am looking for this average for each in column P10, P11, P12, P13.

I try using the averageifs to determine the average based on criteria but i dont think am able to get it right

Appreciate if you could look around the formula

thanks

Book1ilsp.xlsx
BCDEFGHIJKLMNOPQR
901-May-21ILSP Performance Leads Avrg HrsILSP KPI Structure No's Of VoyageTotal Avrg HrsAvrg Hrs
10Avrg Time Spent on Full Deck Jackup Rigs <1618:44Acutual Visitt Trip w/ Full Deck J & Barges11206:1018:44Avergae Time Rigs&Barges#DIV/0!Full 12:0
11Avrg Time Spent on Half Deck Jackup Rigs <109:30Acutual Visitt Trip w/ Half Deck J & Barges46437:459:30Avergae Time Rigs&Barges#DIV/0!Half10:0
12Avrg Time Spent on Drilling Island <104:29Actual Operation Drilling Island33148:004:29Averge Time Drl-Island14:56target10:0
13Avrg Time Spent on Dlight Prd-Island Rigs <108:40Actual Prd-Island Visit D/light12104:008:40PAverge Time Prd-Island14:28Operation10:0
14Avrg Time Spent on D-light Complex <126:31Actual Complex Visit D/light28182:416:31PAverge Time Prd-Complex19:20Operation12:0
15
16YearV-NoSupply ManifestD/PVessel DeptFull Location Arrival & StartDepatureTotal ILSPLocation Total KPI
17MonthMus-NoSupply VesselIssued MonthVSLADNOC Jetty Date & Time HalfSiteDate & Time Date & TimeSpent HrsKPI StructureSiteActual Spent HrsTarget
1801-Jan-213705ADNOC-810Jan-21P01/01/21 09:30HalfUSSC02/01/21 08:5502/01/21 12:153:20Prd-ComplexUSSC7:25
1901-Jan-213705ADNOC-810Jan-21P01/01/21 09:30HalfJopetwill-30002/01/21 13:3502/01/21 19:506:15Prd-ComplexJopetwill-3006:15
2001-Jan-213705ADNOC-810Jan-21P01/01/21 09:30HalfUSSC02/01/21 20:2502/01/21 21:301:05   
2101-Jan-213705ADNOC-810Jan-21P01/01/21 09:30HalfUSSC03/01/21 07:4503/01/21 10:453:00   
2201-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfAl Ghallan Island 02/01/21 07:1002/01/21 12:185:08Drl-IslandAl Ghallan Island 5:08
2301-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfAsseifiya Island 02/01/21 14:1802/01/21 15:000:42Drl-IslandAsseifiya Island 0:42
2401-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfEttouk Island 02/01/21 17:4002/01/21 18:421:02Drl-IslandEttouk Island 1:02
2501-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfUmm Al Anbar03/01/21 07:0003/01/21 10:423:42Drl-IslandUmm Al Anbar3:42
2601-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfDeep Driller-304/01/21 04:1804/01/21 16:4212:24Rigs&BargesDeep Driller-312:24
2701-Dec-203666-OASL SWIFTJan-21D02/01/21 16:00HalfRig Artabhatt-129/12/20 08:1529/12/20 15:207:05Rigs&BargesRig Artabhatt-121:35
2801-Dec-203666-OASL SWIFTJan-21D02/01/21 16:00HalfRig Al Bzoom30/12/20 15:5030/12/20 21:005:10Rigs&BargesRig Al Bzoom5:10
2901-Dec-203666-OASL SWIFTJan-21D02/01/21 16:00HalfRig Artabhatt-131/12/20 04:0031/12/20 18:3014:30   
3001-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfRig Artabhatt-102/01/21 09:4502/01/21 10:551:10Rigs&BargesRig Artabhatt-11:10
3101-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfBarge Pride 02/01/21 13:4002/01/21 16:302:50Rigs&BargesBarge Pride 2:50
3201-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfRig Diyina02/01/21 20:4503/01/21 02:405:55Rigs&BargesRig Diyina5:55
3301-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfDeep Driller-303/01/21 10:1503/01/21 12:101:55Rigs&BargesDeep Driller-39:45
3401-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfDeep Driller-303/01/21 20:1004/01/21 04:007:50   
3501-Jan-213708SMIT LUZONJan-21D01/01/21 20:30Full Rig Hudairiyat02/01/21 16:1804/01/21 16:3048:12Rigs&BargesRig Hudairiyat63:06
3601-Jan-213708SMIT LUZONJan-21D01/01/21 20:30Full Rig Hudairiyat04/01/21 21:0005/01/21 11:5414:54   
3701-Jan-213708SMIT LUZONJan-21D01/01/21 20:30Full Barge Shamal15/01/21 21:0016/01/21 01:064:06Rigs&BargesBarge Shamal4:06
3801-Jan-213709SMIT LUMUTJan-21D01/01/21 19:15HalfRig Al Lulu01/01/21 23:5902/01/21 00:450:46Rigs&BargesRig Al Lulu0:46
3901-Jan-213709SMIT LUMUTJan-21D01/01/21 19:15HalfRig Al Hail02/01/21 11:2502/01/21 13:001:35Rigs&BargesRig Al Hail1:35
4001-Jan-213709SMIT LUMUTJan-21D01/01/21 19:15HalfRig VKN-302/01/21 22:0002/01/21 23:101:10Rigs&BargesRig VKN-31:10
4101-Jan-213709SMIT LUMUTJan-21D01/01/21 19:15HalfRig Al Ghallan03/01/21 08:3504/01/21 03:4519:10Rigs&BargesRig Al Ghallan19:10
4201-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig SMS Faith 03/01/21 06:0003/01/21 08:482:48Rigs&BargesRig SMS Faith 2:48
4301-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig Al Gharbia03/01/21 12:0003/01/21 21:009:00Rigs&BargesRig Al Gharbia9:00
4401-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig Marawwah04/01/21 00:3504/01/21 14:5014:15Rigs&BargesRig Marawwah14:15
4501-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig Yemillah04/01/21 16:3005/01/21 00:017:31Rigs&BargesRig Yemillah7:31
4601-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig Al Hail05/01/21 05:3005/01/21 20:0014:30Rigs&BargesRig Al Hail14:30
4701-Jan-213711MAC PHOENIXJan-21D01/01/21 21:22HalfRig Muhaiyimat02/01/21 04:1302/01/21 15:4511:32Rigs&BargesRig Muhaiyimat11:32
4801-Jan-213711MAC PHOENIXJan-21D01/01/21 21:22HalfDeep Driller-302/01/21 17:3003/01/21 12:1518:45Rigs&BargesDeep Driller-318:45
4901-Jan-213712ADNOC-812Jan-21P01/01/21 21:15HalfBarge Al Hyleh02/01/21 09:4502/01/21 18:108:25Prd-ComplexBarge Al Hyleh8:25
5001-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfBarge MB-102/01/21 10:4502/01/21 15:505:05Prd-ComplexBarge MB-15:35
5101-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfBarge Pesto03/01/21 08:3003/01/21 13:254:55Prd-ComplexBarge Pesto4:55
5201-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfZWSC04/01/21 08:4004/01/21 09:200:40Prd-ComplexZWSC3:45
5301-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfZCSC04/01/21 13:4004/01/21 17:454:05Prd-ComplexZCSC5:57
5401-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfZCSC04/01/21 15:5304/01/21 17:451:52   
5501-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfBarge MB-104/01/21 20:0004/01/21 20:300:30   
ILSP_KPI_Tracking_Data_Entry
Cell Formulas
RangeFormula
J10J10=IFERROR(COUNTIFS($AA$18:$AA$696, $H$16, $CY$18:$CY$696, ">"&$B$2),"")
K10K10=IFERROR(SUMIFS($CY$18:$CY$696,$AA$18:$AA$696,$H$16),"")
L10:L14L10=IFERROR(SUM(K10/J10),"")
J11J11=IFERROR(COUNTIFS($AA$18:$AA$696, $H$17, $CY$18:$CY$696, ">"&$B$2),"")
K11K11=IFERROR(SUMIFS($CY$18:$CY$696,$AA$18:$AA$696,$H$17),"")
J12J12=IFERROR(COUNT($CV$18:$CV$696<>0,$CV$18:$CV$696),"")
K12K12=IFERROR(SUM(CV18:CV696),"")
J13J13=IFERROR(COUNT($DB$18:$DB$696<>0,$DB$18:$DB$696),"")
K13K13=IFERROR(SUM(DB18:DB696),"")
J14J14=IFERROR(COUNT($DE$18:$DE$696<>0,$DE$18:$DE$696),"")
K14K14=IFERROR(SUM(DE18:DE696),"")
E10E10=IFERROR(AVERAGEIFS($CY$18:$CY$696,$AB$18:$AB$696,"<>0",$AA$18:$AA$696,$H$16),"")
E11E11=IFERROR(AVERAGEIFS($CY$18:$CY$696,$AB$18:$AB$696,"<>0",$AA$18:$AA$696,$H$17),"")
E12E12=IFERROR(AVERAGEIF(CV18:CV696,"<>0"),"")
E13E13=IFERROR(AVERAGEIF(DB18:DB696,"<>0"),"")
E14E14=IFERROR(AVERAGEIF(DE18:DE696,"<>0"),"")
P10P10=AVERAGEIFS(P18:P60000,B18:B60000,B9,H18:H60000,Q10,N18:N60000,O10,P18:P60000,"">R10)
P11P11=AVERAGEIFS(P18:P60000,B18:B60000,B9,H18:H60000,Q11,N18:N60000,O11,P18:P60000,"">R11)
P12P12=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O12,$B$18:$B$60000,B9,$P$18:$P$60000,">"&R12)
P13P13=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O13,$B$18:$B$60000,B9,$P$18:$P$60000,">"&R13)
P14P14=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O14,$B$18:$B$60000,B9,$P$18:$P$60000,">"&R14)
E18:E55E18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,8,0),"")
F18:F55F18=IFERROR(VLOOKUP(D18,$S$18:$T$94,2,0),"")
G18:G55G18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,11,0),"")
H18:H55H18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,3,0),"")
N18:N55N18=IFERROR(VLOOKUP(O18,$EL$18:$EM$86,2,0),"")
O18O18=IF(COUNTIFS(I18:$I$18,I18,C18:$C$18,C18)=1,I18,"")
P18:P55P18=IF(O18="","",SUMIFS($L$18:$L$60000,$I$18:$I$60000,I18,$C$18:$C$60000,C18))
O19:O55O19=IF(COUNTIFS(I$18:$I19,I19,C$18:$C19,C19)=1,I19,"")
B18:B55B18=IFERROR(EOMONTH(J18,-1)+1,"")
L18:L55L18=IF(OR(ISBLANK(J18),ISBLANK(K18)), "", K18-J18)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H18:H7467Cell Valuecontains "NAF"textNO
H16:H17Cell Valuecontains "NAF"textNO
H16:H17Cell Valuecontains "M"textNO
H16:H17Cell Valuecontains "Naf"textNO
Cells with Data Validation
CellAllowCriteria
B9List=$ACM$18:$ACM$209
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top