Averageifs with multiple criteria based on KPI target on monthly

L

Lukma

Active Member
Joined
Feb 12, 2020
Messages
259
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Guys

Am Really Grateful with everyone in forum, solving my trouble work easily, i have been able to update my Sheet, i need assistance as am not too sure with the average of my result in Colum P10, P11, P12, P13
My Data Sheet for my task to find the average of Hours for each criteria that exceed the KPI target, the idea is to find the average monthly, so i created a column N18 next to Column O18 to define which section the location site belongs to within the KPI And From Column Q10 & R10 i have create a Hours for each KPI, the idea for this KPI is to find the average for each Criteria that exceed the T KPI target Hours monthly

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

Appreciate if anyone could help out with this solution

Book1
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
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:E41E18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,8,0),"")
F18:F41F18=IFERROR(VLOOKUP(D18,$S$18:$T$94,2,0),"")
G18:G41G18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,11,0),"")
H18:H41H18=IFERROR(VLOOKUP(C18,$DK$18:$DU$60000,3,0),"")
N18:N41N18=IFERROR(VLOOKUP(O18,$EL$18:$EM$86,2,0),"")
O18O18=IF(COUNTIFS(I18:$I$18,I18,C18:$C$18,C18)=1,I18,"")
P18:P41P18=IF(O18="","",SUMIFS($L$18:$L$60000,$I$18:$I$60000,I18,$C$18:$C$60000,C18))
O19:O41O19=IF(COUNTIFS(I$18:$I19,I19,C$18:$C19,C19)=1,I19,"")
B18:B41B18=IFERROR(EOMONTH(J18,-1)+1,"")
L18:L41L18=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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
You must log in or register to reply here.

Similar threads

L
  • Solved
Help with formula Number serial based on criteria monthly
Replies
4
Views
318
Peter_SSs
Peter_SSs
L
  • Question Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
455
Lukma
L
L
  • Solved
Help with IF Formula for my Data.
Replies
5
Views
599
Lukma
L
L
  • Solved
SUMIFS based on multiple criteria and between date
2
Replies
17
Views
921
etaf
etaf
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

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