Lookup with criteria and give actual sum base on two criteria

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
133
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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
can you explain in words with examples , what you are trying to do
B10 for example AVERAGEIFS($CY$18:$CY$696,$AB$18:$AB$696,"<>0",$AA$18:$AA$696,$H$16
How does excel know its JACKUP RIGS ?
CY? AB? etc
No idea whats in those cells
you really need to explain in detail what you are trying to do - a XL2BB outout does not really tell me anything , other than what you have done - NOT what you want and why
Sorry - but the detail is what i need to help
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Lukma

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

If you can see in column P10, P11, P12,P13 am trying to find the average for each base on the KPI

THE kpi is set to find the average of hours exceeding the target

But am not too sure with the averageifs i have use if am actually right with the result


ILSP KPI Performance Tracking.xlsx
BCDEFGHIJKLMNOPQ
901-May-21ILSP Performance Leads Avrg HrsILSP KPI Structure No's Of VoyageTotal Avrg HrsAvrg Hrs44197
10Avrg Time Spent on Full Deck Jackup Rigs <1618:44Acutual Visitt Trip w/ Full Deck J & Barges11206:1018:44Avergae Time Rigs&Barges#DIV/0!#DIV/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!#DIV/0!
12Avrg Time Spent on Drilling Island <104:29Actual Operation Drilling Island33148:004:29Averge Time Drl-Island14:56
13Avrg Time Spent on Dlight Prd-Island Rigs <108:40Actual Prd-Island Visit D/light12104:008:40Averge Time Prd-Island14:28
14Avrg Time Spent on D-light Complex <126:31Actual Complex Visit D/light28182:416:31Averge Time Prd-Complex12:2419:2
1516:0010:0012:0010:00
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
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),"")
Q10Q10=AVERAGEIFS(P18:P35,B18:B35,B9,H18:H35,H16,N18:N35,O10,P18:P35,"">N15)
Q11Q11=AVERAGEIFS(P18:P60000,B18:B60000,B9,H18:H60000,H17,N18:N60000,O11,P18:P60000,"">O15)
P10P10=AVERAGEIFS(P18:P35,B18:B35,B9,H18:H35,H16,N18:N35,O10,P18:P35,"">N15)
P11P11=AVERAGEIFS(P18:P33,B18:B33,B9,H18:H33,H17,N18:N33,O11,P18:P33,"">O15)
P12P12=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O12,$B$18:$B$60000,B9,$P$18:$P$60000,">"&O15)
P13P13=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O13,$B$18:$B$60000,B9,$P$18:$P$60000,">"&O15)
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"),"")
P14P14=AVERAGEIFS(P18:P28,H18:H28,H17,B18:B28,N9,N18:N28,O11,P18:P28,">10:00")
Q14Q14=AVERAGEIFS($P$18:$P$60000,$N$18:$N$60000,O13,$B$18:$B$60000,B9,$P$18:$P$60000,">"&P15)
E18:E46E18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,8,0),"")
F18:F46F18=IFERROR(VLOOKUP(D18,$S$18:$T$94,2,0),"")
G18:G46G18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,11,0),"")
H18:H46H18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,3,0),"")
N18:N46N18=IFERROR(VLOOKUP(O18,$EL$18:$EM$86,2,0),"")
O18O18=IF(COUNTIFS(I18:$I$18,I18,C18:$C$18,C18)=1,I18,"")
P18:P46P18=IF(O18="","",SUMIFS($L$18:$L$60000,$I$18:$I$60000,I18,$C$18:$C$60000,C18))
O19:O46O19=IF(COUNTIFS(I$18:$I19,I19,C$18:$C19,C19)=1,I19,"")
B18:B46B18=IFERROR(EOMONTH(J18,-1)+1,"")
L18:L46L18=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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
sorry , i still have no idea what this could mean
THE kpi is set to find the average of hours exceeding the target
Need to see some worked examples, like i added

think of telling a kid what you are doing with words and why
again
If you can see in column P10, P11, P12,P13 am trying to find the average for each base on the KPI
Whats a BASE whats the KPI

You know what all these things are to you , and what they mean in your spreadsheet, i really don't, and sorry i'm not getting it

SO in words what are you trying to do , with worked examples - as if you are doing it on paper

Something like I put before, with a worked example and cell reference LONG hand

Really sorry again but None of your formulas make any sense

The sheet posted goes from B to Q
as mentioned
But your formulas reference AA, CY , DB, DK etc etc , I have no idea whats in those columns or why you are using
J10J10=IFERROR(COUNTIFS($AA$18:$AA$696, $H$16, $CY$18:$CY$696, ">"&$B$2),"")

I really do want to help , and not trying to be difficult, but unless i know in children type language , i'm not going to be able to

As its a new question, it maybe worth making this thread as solved and open a new one, maybe someone else will then read a new post and have a better idea of what you are trying to do
 

Lukma

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

Sorry for misunderstanding me, from start of my data i was able to get my spread sheet updated to above and i do really appreciate you help, Below is the KPI target set for operation
Average Time Spent on Full Deck Jackup Rigs <16
Average Time Spent on Half Deck Jackup Rigs <10
Average Time Spent on Drilling Island <10
Average Time Spent on Dlight Prd-Island Rigs <10
Average Time Spent on D-light Complex <12

so what am trying to achieve is the average hours that is greater than the target KPI in Column D3, D4, D5, D6 for Each Target Criteria KPI.

ILSP KPI Performance Tracking.xlsx
BCDEFGHIJKLMNOPQ
101-Jan-21
2Avergae Time Rigs&Barges#DIV/0!Full
3Avergae Time Rigs&Barges#DIV/0!Half
4Averge Time Drl-Island13:7
5Averge Time Prd-Island12:10
6Averge Time Prd-Complex21:52
716:0010:0012:0010:00
8
9YearV-NoSupply ManifestD/PVessel DeptFull Location Arrival & StartDepatureTotal ILSPLocation Total KPI
10MonthMus-NoSupply VesselIssued MonthVSLADNOC Jetty Date & Time HalfSiteDate & Time Date & TimeSpent HrsKPI StructureSiteActual Spent HrsTarget
1101-Jan-213705ADNOC-810Jan-21P01/01/21 09:30HalfUSSC02/01/21 08:5502/01/21 12:153:20Prd-ComplexUSSC7:25
1201-Jan-213705ADNOC-810Jan-21P01/01/21 09:30HalfJopetwill-30002/01/21 13:3502/01/21 19:506:15Prd-ComplexJopetwill-3006:15
1301-Jan-213705ADNOC-810Jan-21P01/01/21 09:30HalfUSSC02/01/21 20:2502/01/21 21:301:05 
1401-Jan-213705ADNOC-810Jan-21P01/01/21 09:30HalfUSSC03/01/21 07:4503/01/21 10:453:00 
1501-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
1601-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfAsseifiya Island 02/01/21 14:1802/01/21 15:000:42Drl-IslandAsseifiya Island 0:42
1701-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfEttouk Island 02/01/21 17:4002/01/21 18:421:02Drl-IslandEttouk Island 1:02
1801-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfUmm Al Anbar03/01/21 07:0003/01/21 10:423:42Drl-IslandUmm Al Anbar3:42
1901-Jan-213706ADNOC-224Dec-20D01/01/21 18:48HalfDeep Driller-304/01/21 04:1804/01/21 16:4212:24Rigs&BargesDeep Driller-312:24
2001-Dec-203666-OASL SWIFTJan-21D02/01/21 16:00HalfRig Artabhatt-129/12/20 08:1529/12/20 15:207:05Rigs&BargesRig Artabhatt-121:35
2101-Dec-203666-OASL SWIFTJan-21D02/01/21 16:00HalfRig Al Bzoom30/12/20 15:5030/12/20 21:005:10Rigs&BargesRig Al Bzoom5:10
2201-Dec-203666-OASL SWIFTJan-21D02/01/21 16:00HalfRig Artabhatt-131/12/20 04:0031/12/20 18:3014:30 
2301-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfRig Artabhatt-102/01/21 09:4502/01/21 10:551:10Rigs&BargesRig Artabhatt-11:10
2401-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfBarge Pride 02/01/21 13:4002/01/21 16:302:50Rigs&BargesBarge Pride 2:50
2501-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfRig Diyina02/01/21 20:4503/01/21 02:405:55Rigs&BargesRig Diyina5:55
2601-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfDeep Driller-303/01/21 10:1503/01/21 12:101:55Rigs&BargesDeep Driller-39:45
2701-Jan-213707Z-POWERJan-21D01/01/21 19:00HalfDeep Driller-303/01/21 20:1004/01/21 04:007:50 
2801-Jan-213708SMIT LUZONJan-21D01/01/21 20:30Full Rig Hudairiyat02/01/21 16:1804/01/21 16:3048:12Rigs&BargesRig Hudairiyat63:06
2901-Jan-213708SMIT LUZONJan-21D01/01/21 20:30Full Rig Hudairiyat04/01/21 21:0005/01/21 11:5414:54 
3001-Jan-213708SMIT LUZONJan-21D01/01/21 20:30Full Barge Shamal15/01/21 21:0016/01/21 01:064:06Rigs&BargesBarge Shamal4:06
3101-Jan-213709SMIT LUMUTJan-21D01/01/21 19:15HalfRig Al Lulu01/01/21 23:5902/01/21 00:450:46Rigs&BargesRig Al Lulu0:46
3201-Jan-213709SMIT LUMUTJan-21D01/01/21 19:15HalfRig Al Hail02/01/21 11:2502/01/21 13:001:35Rigs&BargesRig Al Hail1:35
3301-Jan-213709SMIT LUMUTJan-21D01/01/21 19:15HalfRig VKN-302/01/21 22:0002/01/21 23:101:10Rigs&BargesRig VKN-31:10
3401-Jan-213709SMIT LUMUTJan-21D01/01/21 19:15HalfRig Al Ghallan03/01/21 08:3504/01/21 03:4519:10Rigs&BargesRig Al Ghallan19:10
3501-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
3601-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig Al Gharbia03/01/21 12:0003/01/21 21:009:00Rigs&BargesRig Al Gharbia9:00
3701-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig Marawwah04/01/21 00:3504/01/21 14:5014:15Rigs&BargesRig Marawwah14:15
3801-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig Yemillah04/01/21 16:3005/01/21 00:017:31Rigs&BargesRig Yemillah7:31
3901-Jan-213710B-LIBERTY-313Jan-21D02/01/21 12:45NAFRig Al Hail05/01/21 05:3005/01/21 20:0014:30Rigs&BargesRig Al Hail14:30
4001-Jan-213711MAC PHOENIXJan-21D01/01/21 21:22HalfRig Muhaiyimat02/01/21 04:1302/01/21 15:4511:32Rigs&BargesRig Muhaiyimat11:32
4101-Jan-213711MAC PHOENIXJan-21D01/01/21 21:22HalfDeep Driller-302/01/21 17:3003/01/21 12:1518:45Rigs&BargesDeep Driller-318:45
4201-Jan-213712ADNOC-812Jan-21P01/01/21 21:15HalfBarge Al Hyleh02/01/21 09:4502/01/21 18:108:25Prd-ComplexBarge Al Hyleh8:25
4301-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfBarge MB-102/01/21 10:4502/01/21 15:505:05Prd-ComplexBarge MB-15:35
4401-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfBarge Pesto03/01/21 08:3003/01/21 13:254:55Prd-ComplexBarge Pesto4:55
4501-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfZWSC04/01/21 08:4004/01/21 09:200:40Prd-ComplexZWSC3:45
4601-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfZCSC04/01/21 13:4004/01/21 17:454:05Prd-ComplexZCSC5:57
4701-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfZCSC04/01/21 15:5304/01/21 17:451:52 
4801-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfBarge MB-104/01/21 20:0004/01/21 20:300:30 
4901-Jan-213713A-HERCULESJan-21P02/01/21 00:30HalfZWSC05/01/21 08:4005/01/21 11:453:05 
5001-Jan-211LCT-TARFFAHJan-21P03/01/21 07:10Full Zirku Island03/01/21 20:4004/01/21 08:5012:10Prd-IslandZirku Island12:10
5101-Jan-211LCT-TARFFAHJan-21P03/01/21 07:10Full Ettouk Island 04/01/21 17:4005/01/21 08:2514:45Drl-IslandEttouk Island 14:45
5201-Jan-212ADNOC-850Jan-21P03/01/21 13:15HalfACPT03/01/21 23:1504/01/21 01:252:10Prd-ComplexACPT16:35
5301-Jan-212ADNOC-850Jan-21P03/01/21 13:15HalfUZWSP04/01/21 02:5004/01/21 03:450:55Prd-ComplexUZWSP0:55
5401-Jan-212ADNOC-850Jan-21P03/01/21 13:15HalfACPT04/01/21 08:3004/01/21 18:3010:00 
5501-Jan-212ADNOC-850Jan-21P03/01/21 13:15HalfACPT05/01/21 07:2505/01/21 11:504:25 
5601-Jan-212ADNOC-850Jan-21P03/01/21 13:15HalfUZNSP05/01/21 13:0005/01/21 15:302:30Prd-ComplexUZNSP2:30
5701-Jan-213ADNOC-510Jan-21D02/01/21 14:20Full Rig Sms Mariam 03/01/21 07:2004/01/21 02:1518:55Rigs&BargesRig Sms Mariam 18:55
5801-Jan-214A-GRACEJan-21D02/01/21 06:15Full Rig Makasib03/01/21 05:0305/01/21 12:0855:05Rigs&BargesRig Makasib55:05
5901-Jan-215MARCAP-2Jan-21P02/01/21 17:35Full ZWSC03/01/21 07:3003/01/21 14:206:50Prd-ComplexZWSC27:10
6001-Jan-215MARCAP-2Jan-21P02/01/21 17:35Full ZWSC04/01/21 07:4004/01/21 17:309:50 
6101-Jan-215MARCAP-2Jan-21P02/01/21 17:35Full Barge Pesto04/01/21 18:0804/01/21 18:550:47Prd-ComplexBarge Pesto0:47
6201-Jan-215MARCAP-2Jan-21P02/01/21 17:35Full ZWSC05/01/21 07:5505/01/21 18:2510:30 
6301-Jan-216QMS DELTAJan-21D02/01/21 21:50HalfRig Al Ghallan03/01/21 10:5003/01/21 11:250:35Rigs&BargesRig Al Ghallan0:35
6401-Jan-216QMS DELTAJan-21D02/01/21 21:50HalfRig Yemillah03/01/21 12:3003/01/21 14:001:30Rigs&BargesRig Yemillah1:30
6501-Jan-216QMS DELTAJan-21D02/01/21 21:50HalfRig Al Hail04/01/21 01:1004/01/21 17:3016:20Rigs&BargesRig Al Hail16:20
6601-Jan-216QMS DELTAJan-21D02/01/21 21:50HalfRig Makasib05/01/21 19:3006/01/21 07:4012:10Rigs&BargesRig Makasib12:10
6701-Jan-217ADNOC-1011Jan-21D03/01/21 03:40HalfAl Qatia Island 03/01/21 19:2003/01/21 22:002:40Drl-IslandAl Qatia Island 11:30
6801-Jan-217ADNOC-1011Jan-21D03/01/21 03:40HalfBu Sikeen Island04/01/21 07:1004/01/21 10:002:50Drl-IslandBu Sikeen Island2:50
6901-Jan-217ADNOC-1011Jan-21D03/01/21 03:40HalfAl Qatia Island 04/01/21 13:4004/01/21 22:308:50 
7001-Jan-217ADNOC-1011Jan-21D03/01/21 03:40HalfAl Ghallan Island 05/01/21 08:4505/01/21 12:003:15Drl-IslandAl Ghallan Island 3:15
7101-Jan-218QMS NEPTUNEJan-21D02/01/21 19:00HalfRig Muhaiyimat02/01/21 23:4503/01/21 01:502:05Rigs&BargesRig Muhaiyimat2:05
7201-Jan-218QMS NEPTUNEJan-21D02/01/21 19:00HalfRig SMS Faith 03/01/21 14:0003/01/21 19:255:25Rigs&BargesRig SMS Faith 5:25
7301-Jan-218QMS NEPTUNEJan-21D02/01/21 19:00HalfRig Yemillah03/01/21 22:0504/01/21 03:004:55Rigs&BargesRig Yemillah4:55
7401-Jan-218QMS NEPTUNEJan-21D02/01/21 19:00HalfRig Al Gharbia04/01/21 06:3004/01/21 17:2510:55Rigs&BargesRig Al Gharbia10:55
7501-Jan-218QMS NEPTUNEJan-21D02/01/21 19:00HalfRig Qarnin04/01/21 20:5505/01/21 12:0015:05Rigs&BargesRig Qarnin15:05
7601-Jan-219ADNOC-221Jan-21D03/01/21 00:45HalfRig Artabhatt-103/01/21 09:4503/01/21 14:254:40Rigs&BargesRig Artabhatt-14:40
7701-Jan-219ADNOC-221Jan-21D03/01/21 00:45HalfRig High Island-703/01/21 15:0003/01/21 15:500:50Rigs&BargesRig High Island-70:50
7801-Jan-219ADNOC-221Jan-21D03/01/21 00:45HalfRig Sms Mariam 04/01/21 04:5005/01/21 14:5034:00Rigs&BargesRig Sms Mariam 34:00
7901-Jan-219ADNOC-221Jan-21D03/01/21 00:45HalfRig Junana06/01/21 04:0006/01/21 16:2012:20Rigs&BargesRig Junana12:20
8001-Jan-219ADNOC-221Jan-21D03/01/21 00:45HalfDeep Driller-206/01/21 18:0506/01/21 23:305:25Rigs&BargesDeep Driller-25:25
8101-Jan-2110SEACOR ALPSJan-21D03/01/21 14:10Full Rig Butinah 04/01/21 19:3005/01/21 18:0522:35Rigs&BargesRig Butinah 22:35
Sheet4
Cell Formulas
RangeFormula
D2D2=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E2,N11:N28,C2,P11:P28,"">B7)
D3D3=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E3,N11:N28,C2,P11:P28,"">B7)
D4D4=AVERAGEIFS($P$11:$P$59993,$N$11:$N$59993,C4,$B$11:$B$59993,B1,$P$11:$P$59993,">"&C7)
D5D5=AVERAGEIFS($P$11:$P$59993,$N$11:$N$59993,C5,$B$11:$B$59993,B1,$P$11:$P$59993,">"&E7)
D6D6=AVERAGEIFS($P$11:$P$59993,$N$11:$N$59993,C6,$B$11:$B$59993,B1,$P$11:$P$59993,">"&D7)
P11:P81P11=IF(O11="","",SUMIFS($L$11:$L$60000,$I$11:$I$60000,I11,$C$11:$C$60000,C11))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H11:H81Cell Valuecontains "NAF"textNO
H9:H10Cell Valuecontains "NAF"textNO
H9:H10Cell Valuecontains "M"textNO
H9:H10Cell Valuecontains "Naf"textNO
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Average Time Spent on Full Deck Jackup Rigs <16

To Average the above in words is
Take the average of the Time Spent ( in Column P) , where the Criteria is "FULL" ( in column H ) And Criteria "Jackup Rigs" (NO IDEA which column has Jackup Rigs) and where Something is less than 16 ( No idea what column to find the less than 16 in )

your formula in D2

=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E2,N11:N28,C2,P11:P28,"">B7)

Take the average from column P, criteria column B dates that are equal to the cell in B1 which is the date , and must = 1/jan/21 and also look at the column H (FULL /HALF ) and Match E2 which is FULL , and also meet column N ILSP and match for C2 , rigs&barges , where the Time in P is ?????
Not sure what "">B7 , is that that to make it greater than 16:00 - ">"&B7

So that should be , note the red
=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E2,N11:N28,C2,P11:P28,">"&B7)

BUT the words say
Average Time Spent on Full Deck Jackup Rigs <16
Less than 16 - and in B2 you have greater than 16

if you want less than
=AVERAGEIFS(P11:P28,B11:B28,B1,H11:H28,E2,N11:N28,C2,P11:P28,"<"&B7)


Average Time Spent on Half Deck Jackup Rigs <10
Average Time Spent on Drilling Island <10
Average Time Spent on Dlight Prd-Island Rigs <10
Average Time Spent on D-light Complex <12
 

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thanks So much Etaf

Yes i totally agreed with you though the write up was mess up by the writer not understanding what they require which i have giving my view to them

But in operation they are trying to get the location that have exceed the Hours of 16 hrs. to complete there work and then find the average hours they have spent

Well am so grateful for your time and patient and the suggest formula is right so am going to use that to define my average to them.

Thanks so much for your time.

REgards
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

glad we resolved the issue
 

Lukma

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

Thanks So Much well just one more and am really sorry bothering you, I need to a solution in Column F11 with IF formula
and my Idea is to Extract the exceed hours based on criteria of the KPI

I need a formula that subtract the criteria and get me only the Exceeded hours

IF column E11 is equal to Column C11 and matches D4 then it should subtract D5 from E11, and IF it match D5 then it should subtract F7 from E11 and If match D6 then it should Subtract E7 from E11 and IF E11 is Equal to C matches D2 and IF B11 Matches C8 then it should Subtract C7 from E11 and IF E11 equal to C11 and B11 equal to D8 then it should Subtract D7 from E11

I hope this can be done with if formula


ILSP KPI Performance Tracking.xlsx
BCDEF
2Avergae Time Rigs&Barges
3Avergae Time Rigs&Barges
4Averge Time Drl-Island
5Averge Time Prd-Island
6Averge Time Prd-Complex
716:0010:0012:0010:00
8FullHalf
9ILSPLocation Total KPI
10KPI StructureSiteActual Spent HrsTarget
11FullPrd-ComplexUSSC3:20
12FullPrd-ComplexJopetwill-3006:15
13Full
14Full
15FullDrl-IslandAl Ghallan Island 8:23
16FullDrl-IslandAsseifiya Island 0:42
17FullDrl-IslandEttouk Island 15:47
18FullDrl-IslandUmm Al Anbar3:42
19FullRigs&BargesDeep Driller-333:04
20FullRigs&BargesRig Artabhatt-112:55
21FullRigs&BargesRig Al Bzoom5:10
22
23HalfRigs&BargesRig Artabhatt-112:55
24HalfRigs&BargesBarge Pride 2:50
25HalfRigs&BargesRig Diyina5:55
26HalfRigs&BargesDeep Driller-333:04
27
28FullRigs&BargesRig Hudairiyat48:12
29
30FullRigs&BargesBarge Shamal4:06
31FullRigs&BargesRig Al Lulu0:46
32FullRigs&BargesRig Al Hail32:25
33FullRigs&BargesRig VKN-31:10
34FullRigs&BargesRig Al Ghallan19:45
35HalfRigs&BargesRig SMS Faith 8:13
36HalfRigs&BargesRig Al Gharbia19:55
37HalfRigs&BargesRig Marawwah14:15
38HalfRigs&BargesRig Yemillah13:56
39HalfRigs&BargesRig Al Hail32:25
40HalfRigs&BargesRig Muhaiyimat13:37
41HalfRigs&BargesDeep Driller-333:04
42HalfPrd-ComplexBarge Al Hyleh8:25
43HalfPrd-ComplexBarge MB-15:05
44HalfPrd-ComplexBarge Pesto5:42
45HalfPrd-ComplexZWSC7:30
46HalfPrd-ComplexZCSC4:05
47HalfPrd-IslandZirku Island12:10
48HalfPrd-ComplexACPT2:10
49HalfPrd-ComplexUZWSP0:55
50HalfPrd-ComplexUZNSP2:30
51HalfRigs&BargesRig Sms Mariam 52:55
52HalfRigs&BargesRig Makasib67:15
53HalfPrd-ComplexZWSC7:30
54HalfPrd-ComplexBarge Pesto5:42
55Half
56HalfRigs&BargesRig Al Ghallan19:45
57HalfRigs&BargesRig Yemillah13:56
58HalfRigs&BargesRig Al Hail32:25
59HalfRigs&BargesRig Makasib67:15
60HalfDrl-IslandAl Qatia Island 2:40
61HalfDrl-IslandBu Sikeen Island2:50
62Half
63HalfDrl-IslandAl Ghallan Island 8:23
64HalfRigs&BargesRig Muhaiyimat13:37
65HalfRigs&BargesRig SMS Faith 8:13
66HalfRigs&BargesRig Yemillah13:56
67HalfRigs&BargesRig Al Gharbia19:55
68FullRigs&BargesRig Qarnin15:05
69FullRigs&BargesRig Artabhatt-112:55
70FullRigs&BargesRig High Island-70:50
71FullRigs&BargesRig Sms Mariam 52:55
72FullRigs&BargesRig Junana12:20
73FullRigs&BargesDeep Driller-25:25
74FullRigs&BargesRig Butinah 22:35
Sheet4
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
IF column E11 is equal to Column C11 and matches D4 then it should subtract D5 from E11, and IF it match D5 then it should subtract F7 from E11 and If match D6 then it should Subtract E7 from E11 and IF E11 is Equal to C matches D2 and IF B11 Matches C8 then it should Subtract C7 from E11 and IF E11 equal to C11 and B11 equal to D8 then it should Subtract D7 from E11
Nested IF = IF( TEST , TRUE , IF( TEST, TRUE , IF( TEST , TRUE , FALSE )))


IF column E11 is equal to Column C11 and matches D4 then it should subtract D5 from E11,
and IF it match D5 then it should subtract F7 from E11
and If match D6 then it should Subtract E7 from E11
and IF E11 is Equal to C matches D2 and IF B11 Matches C8 then it should Subtract C7 from E11
and IF E11 equal to C11 and B11 equal to D8 then it should Subtract D7 from E11

BUT

but the cells are text and time and your saying E11 - D5 E11 is 3:20 time - D5 which is a word prd-complex
so again, i dont know what you want , as that will never work.
 

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thanks but what i need is to subtract the time if it meet the criteria KPI and to give me the Exceeded hours. in Column F11 and to drag it down

Rgeard
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,394
Messages
5,769,828
Members
425,574
Latest member
grimeslisa

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
Top