# formula to extract actual average based on multiple criteria and ignore blank cell

Status
Not open for further replies.

#### Lukma

##### New Member
Hi Guys am not too sure with my average result i need a formula that can find the actual average for each multiple criteria monthly
my datasheet am looking for the average for each criteria
Average Time on column L14 for Full deck based on Jackup Rigs & barges Column H14
Average Time on column L14 for Half deck based on Jackup Rigs & barges Column H14
Average Time on column L14 for Island Rigs & barges Column H14
Average Time on column L14 for Complex Rigs & barges Column H14
Average Time on column L14 for Prd-Island Rigs & barges Column H14

Excel Formula:
``=IFERROR(AVERAGEIFS(\$L\$14:\$L\$61500,\$G\$14:\$G\$61500,\$G\$8,\$H\$14:\$H\$61500,\$H\$7,\$B\$14:\$B\$61500,\$B\$7),"")``

ILSP Performance Tracking 2020.xlsx
ABCDEFGHIJKLM
1
2
3HRILSP Level 1&2 Vessel Peformance Tracking 2020
4
5
6Monthly Summary ILSP Monthly Summary Report Island Performan LeadsPercentage
701-Dec-20IslandJackup Rigs & Barge Total Monthly Visit on Island30
8Avrg Time Full Deck 5:24DFull 14:20Total Meet Target KPI2790%
9Avrg Time Half Deck6:02DHalf9:54Total Exceed Target KPI310%
10T0:0110:100:0110:1010:0016:10
11T10:00200:008:0011:0016:10200:00
12MYearManifestVoyage Supply D/PFullLang RigsLocation Arrival & StartDepatureTotal Hrs
13NafMonthIssued MonthMUS No.Vessel VSLHalfRigs/ComplexSite Date & Time Date & TimeSpent
1401-Nov-20Nov-203135B-HOMEREDHalfJackup Rigs & Barge Rig Muhaiyimat01/11/20 21:4501/11/20 22:150:300
1501-Nov-20Nov-203135B-HOMEREDHalfJackup Rigs & Barge Rig Al Lulu01/11/20 22:4504/11/20 16:1065:2565
1601-Nov-20Nov-203136ADNOC-222DHalfJackup Rigs & Barge Rig Shuweihat04/11/20 18:0005/11/20 01:357:357
1701-Nov-20Nov-203136ADNOC-222DHalfJackup Rigs & Barge Barge Bahia05/11/20 07:0005/11/20 13:406:406
1801-Nov-20Nov-203136ADNOC-222DHalfJackup Rigs & Barge Deep Driller-205/11/20 19:0006/11/20 22:1527:1527
1901-Nov-20Nov-203136ADNOC-222DHalfJackup Rigs & Barge Rig Artabhatt-107/11/20 00:1007/11/20 02:001:501
2001-Nov-20Nov-203136ADNOC-222DHalfIslandUmm Al Anbar07/11/20 08:3507/11/20 11:302:552
2101-Nov-20Nov-203137MAC PHOENIXDFull Jackup Rigs & Barge Rig Sms Mariam 04/11/20 02:0605/11/20 04:5026:4426
2201-Nov-20Nov-203137MAC PHOENIXDFull Jackup Rigs & Barge Rig PN-805/11/20 06:0605/11/20 10:244:184
2301-Nov-20Nov-203138ADNOC-510DHalfJackup Rigs & Barge Rig Al Ghallan01/11/20 20:4502/11/20 18:1021:2521
2401-Nov-20Nov-203138ADNOC-510DHalfJackup Rigs & Barge Deep Driller-202/11/20 19:3503/11/20 01:255:505
2501-Nov-20Nov-203138ADNOC-510DHalfJackup Rigs & Barge Deep Driller-303/11/20 12:1503/11/20 20:208:058
2601-Nov-20Nov-203139ADNOC-1011DHalfIslandAsseifiya Island 02/11/20 05:0502/11/20 07:502:452
2701-Nov-20Nov-203139ADNOC-1011DHalfIslandUmm Al Anbar02/11/20 13:1502/11/20 23:009:459
2801-Nov-20Nov-203139ADNOC-1011DHalfIslandEttouk Island 03/11/20 07:2503/11/20 14:507:257
2901-Nov-20Nov-203139ADNOC-1011DHalfIslandUmm Al Anbar03/11/20 22:1004/11/20 02:304:204
3001-Nov-20Nov-203140ADNOC-1010DHalfIslandBu Sikeen Island02/11/20 19:2003/11/20 04:409:209
3101-Nov-20Nov-203140ADNOC-1010DHalfIslandAl Qatia Island 03/11/20 06:3003/11/20 17:0510:3510
3201-Nov-20Nov-203141LCT-TARFFAHPFull Prd-Island Arzanah Island 03/11/20 14:2004/11/20 16:0025:4025
3301-Nov-20Nov-203141LCT-TARFFAHPFull Prd-Island Zirku Island04/11/20 21:2505/11/20 15:3518:1018
3501-Nov-20Nov-203142ADNOC-850PHalfJackup Rigs & Barge Rig Artabhatt-103/11/20 12:1503/11/20 12:500:350
3601-Nov-20Nov-203142ADNOC-850PHalfComplexBarge Al Ghweifat04/11/20 12:5004/11/20 19:156:256
3801-Nov-20Nov-203143Z-OCEANPHalfComplexUmm Lulu03/11/20 06:4203/11/20 18:0011:1811
3901-Nov-20Nov-203144Z-EMPERORDHalfJackup Rigs & Barge Rig Al Hail03/11/20 12:4003/11/20 22:4010:0010
4001-Nov-20Nov-203144Z-EMPERORDHalfJackup Rigs & Barge Rig VKN-303/11/20 23:4005/11/20 00:0024:2024
4101-Nov-20Nov-203144Z-EMPERORDHalfJackup Rigs & Barge Barge Scirocco05/11/20 11:4506/11/20 22:4034:5534
4201-Nov-20Nov-203145SEACOSCO OHIODNafJackup Rigs & Barge Rig VKN-304/11/20 12:3004/11/20 13:301:001
4301-Nov-20Nov-203145SEACOSCO OHIODNafJackup Rigs & Barge Rig Makasib04/11/20 14:4907/11/20 10:4567:5667
4401-Nov-20Nov-203145SEACOSCO OHIODNafJackup Rigs & Barge Rig VKN-307/11/20 12:0507/11/20 15:163:113
4501-Nov-20Nov-203145SEACOSCO OHIODNafJackup Rigs & Barge Rig Makasib08/11/20 00:1311/11/20 21:1192:5892
4601-Nov-20Nov-203145SEACOSCO OHIODNafJackup Rigs & Barge Rig VKN-312/11/20 15:0013/11/20 09:3018:3018
4701-Nov-20Nov-203145SEACOSCO OHIODNafJackup Rigs & Barge Rig Hudairiyat13/11/20 11:0013/11/20 17:106:106
5101-Nov-20Nov-203147ADNOC-221DHalfIslandAl Ghallan Island 03/11/20 21:0504/11/20 01:304:254
Backup Date
Cell Formulas
RangeFormula
L7L7=SUMIFS(R14:R73,N14:N73,N8)
L8L8=SUMIFS(P14:P73,N14:N73,N8)
M8:M9M8=L8/\$L\$7
L9L9=SUMIFS(Q14:Q73,N14:N73,N8)
E8E8=IFERROR(AVERAGEIFS(\$L\$14:\$L\$61500,\$F\$14:\$F\$61500,\$F\$8,\$H\$14:\$H\$61500,\$E\$7,\$B\$14:\$B\$61500,\$B\$7),"")
E9E9=IFERROR(AVERAGEIFS(\$L\$14:\$L\$61500,\$G\$14:\$G\$61500,\$G\$9,\$H\$14:\$H\$61500,\$E\$7,\$B\$14:\$B\$61500,\$B\$7),"")
H8H8=IFERROR(AVERAGEIFS(\$L\$14:\$L\$61500,\$G\$14:\$G\$61500,\$G\$8,\$H\$14:\$H\$61500,\$H\$7,\$B\$14:\$B\$61500,\$B\$7),"")
H9H9=IFERROR(AVERAGEIFS(\$L\$14:\$L\$61500,\$G\$14:\$G\$61500,\$G\$9,\$H\$14:\$H\$61500,\$H\$7,\$B\$14:\$B\$61500,\$B\$7),"")
F14:F51F14=IFERROR(VLOOKUP(E14,\$IJ\$17:\$IK\$96,2,0),"")
G14:G51G14=IFERROR(VLOOKUP(D14,\$DM\$14:\$DQ\$6017,5,0),"")
H14:H51H14=IFERROR(VLOOKUP(I14,\$IB\$17:\$IE\$280,2,0),"")
L14:L51L14=IF(OR(ISBLANK(J14),ISBLANK(K14)), "", K14-J14)
M14:M51M14=TEXT(ABS(K14-J14),"[h]")
B14:B51B14=IF(ISBLANK(C14)," ",DATE(YEAR(C14),MONTH(C14),0+1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G12:G1643Cell Valuecontains "M"textNO
G12:G1643Cell Valuecontains "Naf"textNO
Cells with Data Validation
CellAllowCriteria
B7:D7List=Info_Data!\$H\$4:\$H\$32

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Fluff

##### MrExcel MVP, Moderator
Status
Not open for further replies.

Replies
2
Views
96
Replies
1
Views
106
Replies
19
Views
190
Replies
3
Views
72
Replies
2
Views
230

1,126,998
Messages
5,622,088
Members
415,875
Latest member
Tarali

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

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