Excel VBA - Calculate average in column excluding rows on different columns

A

aenemus

New Member
Joined
Mar 10, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello,

Can someone help me to create a code to calculate the average on column T exlcuding the rows that are blank on column E and with "PC" on column R?


Captura de ecrã 2023-03-21 020922.jpg


Thank you
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
bebo021999 said:
How we can test without sample sheet?
try to attach one.
Click to expand...

mapa.xlsm
ABCDEFGHIJKLMNOPRTUVW
1Data plan.registroHr.planej.registroNome de ClienteNº RemessaNº transporteYARD CAR IDData RegistoHora RegistoEsperaHora EntradaData Saída MercHora Saída MercData de SaídaHora SaídaPermanênciaVCartão MotoristaEsperaCarga/DescargaFinalizaçãoTruck residence time
217.03.202313:00:00Operador Logistico Carregado2217015009195106287129229317.03.202312:04:5600:03:0812:08:0417.03.202314:10:0317.03.202314:35:1302:27:0900:03:0802:01:5900:25:1002:30:17
317.03.2023Pingo Doce Distribuição Regional Su2219160948195106286929224017.03.202302:43:3800:12:1502:55:5317.03.202303:39:1717.03.202303:48:0800:52:1500:12:1500:43:2400:08:5101:04:30
417.03.202319:00:00Pingo Doce Azambuja Ambiente JIT VN2219160953195106286629229517.03.202312:58:4300:19:4813:18:3117.03.202314:00:4817.03.202314:11:2700:52:5600:19:4800:42:1700:10:3901:12:44
517.03.202319:00:00Pingo Doce Azambuja Ambiente JIT VN2219160949195106286529230317.03.202317:55:1200:03:4317:58:5517.03.202318:34:0117.03.202318:34:1400:35:1900:03:4300:35:0600:00:1300:39:02
617.03.202316:00:00Pingo Doce Azambuja Ambiente JIT VN2219160945195106286429230017.03.202315:31:1700:11:1815:42:3517.03.202316:07:2717.03.202316:10:5700:28:2200:11:1800:24:5200:03:3000:39:40
717.03.202313:00:00Pingo Doce Azambuja Ambiente JIT VN2219160943195106286329229617.03.202313:02:4300:09:4113:12:2417.03.202313:43:3617.03.202313:49:0600:36:4200:09:4100:31:1200:05:3000:46:23
817.03.202301:00:00Pingo Doce Novo Armazém Regional do2219160947195106286129223817.03.202301:23:4400:22:4801:46:3217.03.202302:45:4417.03.202302:55:1001:08:3800:22:4800:59:1200:09:2601:31:26
917.03.202301:00:00Pingo Doce Novo Armazém Regional do2219160944195106286029223617.03.202300:19:4300:12:3200:32:1517.03.202301:29:3117.03.202301:39:2801:07:1300:12:3200:57:1600:09:5701:19:45
1017.03.202315:00:00Operador Logistico Canelas2217015008195106285729229917.03.202314:50:5000:10:2015:01:1017.03.202315:43:5517.03.202315:59:1800:58:0800:10:2000:42:4500:15:2301:08:28
1117.03.202315:00:00Operador Logistico Carregado2217015000195106285529230117.03.202315:44:3100:20:2816:04:5917.03.202317:19:2917.03.202317:47:3901:42:40C00:20:2801:14:3000:28:1002:03:08
1217.03.202311:00:00Novadis Bombarral2219161244195106285129228617.03.202310:38:2900:04:2410:42:5317.03.202312:33:3217.03.202312:35:2101:52:28C00:04:2401:50:3900:01:4901:56:52
1317.03.202305:00:00Paulino Tocha Coimbra2219161196195106285029224317.03.202303:25:4000:09:5303:35:3317.03.202304:57:0617.03.202305:07:0601:31:33B00:09:5301:21:3300:10:0001:41:26
1417.03.202308:00:00SCC Ofertas Marketing Via Transport2021028833195106284117.03.202308:14:4300:05:2008:20:0317.03.202309:17:4517.03.202309:18:0200:57:5900:05:2000:57:4200:00:1701:03:19
1517.03.202310:00:00Operador Logistico Carregado2217014999195106284029228917.03.202311:26:4900:06:0211:32:5117.03.202312:43:1317.03.202312:53:3301:20:4200:06:0201:10:2200:10:2001:26:44
1617.03.202306:00:00Operador Logistico Carregado2217014998195106283729226317.03.202307:43:4900:03:4807:47:3717.03.202308:19:0817.03.202308:27:4000:40:0300:03:4800:31:3100:08:3200:43:51
1717.03.202310:00:00ITMP Entreposto Alcanena2219160770195106281529227017.03.202309:51:0500:28:5710:20:0217.03.202311:39:4017.03.202311:51:1901:31:1700:28:5701:19:3800:11:3902:00:14
1817.03.202310:00:00ITMP Entreposto Alcanena2219160558195106281429226717.03.202309:09:5800:10:1609:20:1417.03.202310:32:5917.03.202310:47:2301:27:09F00:10:1601:12:4500:14:2401:37:25
1917.03.202310:00:00SCC Ofertas Off-Trade Via Transport2021029117195106281329226817.03.202309:26:0200:45:4410:11:4617.03.202311:07:3417.03.202311:14:3701:02:5100:45:4400:55:4800:07:0301:48:35
2017.03.202310:00:00Operador Logistico Carregado2217014997195106274929226617.03.202309:07:4600:14:4209:22:2817.03.202310:09:1817.03.202310:16:1800:53:5000:14:4200:46:5000:07:0001:08:32
2117.03.202305:00:00Dia Armazém Alverca II22191610542202486729225317.03.202305:32:3900:10:3705:43:1617.03.202306:23:3917.03.202306:48:3001:05:14A00:10:3700:40:2300:24:5101:15:51
2217.03.202308:00:00Recheio Abóboda22191612592202486329226917.03.202309:37:1600:10:3609:47:5217.03.202311:58:0617.03.202312:22:1502:34:23A00:10:3602:10:1400:24:0902:44:59
2317.03.202310:00:00Recheio Torres Vedras22191611912202485729228317.03.202310:24:3500:13:0610:37:4117.03.202312:02:0417.03.202312:12:5101:35:10F00:13:0601:24:2300:10:4701:48:16
2417.03.202305:00:00Refrigerantes Baía - Armazém Barqui22191612632202485529224517.03.202303:59:1200:21:2604:20:3817.03.202304:58:5617.03.202305:11:1100:50:3300:21:2600:38:1800:12:1501:11:59
2517.03.202305:00:00Refrigerantes Baía - Armazém Barqui22191613592202485129225117.03.202305:19:1100:12:3705:31:4817.03.202307:19:5617.03.202307:46:5302:15:05F00:12:3701:48:0800:26:5702:27:42
2617.03.202304:00:00Novadis Setubal22191612722202483929221916.03.202320:29:4600:45:1421:15:0016.03.202322:30:0717.03.202308:41:4911:26:49APC00:45:1401:15:07####################
2717.03.2023Novadis Lisboa22191612422202483029221816.03.202320:29:1300:36:4721:06:0016.03.202322:37:0717.03.202306:40:0609:34:06CPC00:36:4701:31:07####################
2817.03.2023Novadis Setubal22191612512202481229221716.03.202320:28:4000:26:2020:55:0016.03.202322:19:0117.03.202307:20:3910:25:39CPC00:26:2001:24:01####################
2917.03.2023Novadis Setubal22191612522202477429221416.03.202320:26:4501:48:1522:15:0017.03.202302:07:5617.03.202306:53:2908:38:29BPC01:48:15##########04:45:33##########
3017.03.202307:00:00Novadis Grândola22191612542202474029226217.03.202307:10:2800:05:1807:15:4617.03.202308:39:5717.03.202308:55:5301:40:07B00:05:1801:24:1100:15:5601:45:25
3117.03.202308:00:00Novadis Vila Nova de Gaia22191612472202472029229017.03.202311:29:4300:12:3611:42:1917.03.202312:21:4017.03.202312:43:3301:01:1400:12:3600:39:2100:21:5301:13:50
3217.03.2023Novadis Lisboa22191612402202470429222216.03.202320:31:2700:53:3321:25:0016.03.202322:31:2017.03.202307:33:3010:08:30APC00:53:3301:06:20####################
3317.03.202307:00:00Novadis Beja22191612562202464129226017.03.202306:44:2200:04:2106:48:4317.03.202309:09:5917.03.202309:33:0802:44:25B00:04:2102:21:1600:23:0902:48:46
3417.03.202304:00:00Novadis Setubal22191612502202464029222116.03.202320:30:5101:14:0921:45:0017.03.202302:29:4317.03.202311:21:4713:36:47APC01:14:09##########08:52:04##########
3517.03.2023Novadis Setubal22191612532202462829222016.03.202320:30:1201:29:4822:00:0016.03.202323:25:4917.03.202306:25:2408:25:24PC01:29:4801:25:49####################
3617.03.202307:00:00Recheio Évora22191603332202461629226117.03.202307:04:5700:06:1207:11:0917.03.202307:43:0017.03.202308:14:3801:03:29A00:06:1200:31:5100:31:3801:09:41
3717.03.202307:00:00Novadis Beja22191612732202456729225617.03.202306:05:1900:07:2306:12:4217.03.202307:02:3917.03.202307:13:0001:00:1800:07:2300:49:5700:10:2101:07:41
3817.03.202305:00:00Novadis Leiria22191612382202454429225817.03.202306:26:0000:09:5206:35:5217.03.202309:02:0917.03.202309:03:1102:27:1900:09:5202:26:1700:01:0202:37:11
3917.03.202311:00:00Novadis Bombarral22191612452202450829228117.03.202310:08:1700:07:5310:16:1017.03.202311:45:1317.03.202311:59:1601:43:06A00:07:5301:29:0300:14:0301:50:59
4017.03.202306:00:00Novadis Lisboa22191612432202447129225417.03.202305:34:4500:07:1205:41:5717.03.202307:32:4317.03.202308:04:0002:22:03L00:07:1201:50:4600:31:1702:29:15
4117.03.202306:00:00Novadis Lisboa22191612412202446829225717.03.202306:08:2400:06:2906:14:5317.03.202309:00:5217.03.202309:12:1202:57:19L00:06:2902:45:5900:11:2003:03:48
4217.03.202301:00:00Novadis Algoz22191612712202440129222616.03.202320:33:4904:01:1100:35:0017.03.202301:46:3617.03.202305:37:4205:02:42DPC04:01:1101:11:3603:51:06##########
4317.03.202305:00:00Novadis Beja22191612552202437829224217.03.202303:17:2900:07:2003:24:4917.03.202304:54:1317.03.202305:14:1701:49:28C00:07:2001:29:2400:20:0401:56:48
4417.03.202301:00:00Sarjoi - Armazém Guarda22191612282202436429224416.03.202320:32:0803:07:5223:40:0017.03.202305:29:0317.03.202306:11:0506:31:05DPC03:07:52##########00:42:02##########
4517.03.202308:00:00Tofasil Subdistribuidor22191612572202434929228417.03.202310:32:4900:12:5210:45:4117.03.202312:55:0017.03.202313:04:1602:18:35F00:12:5202:09:1900:09:1602:31:27
4617.03.202305:00:00Novadis Castelo Branco22191612372202421229225217.03.202305:28:4100:06:3105:35:1217.03.202306:47:5617.03.202307:00:5301:25:41C00:06:3101:12:4400:12:5701:32:12
4717.03.202306:00:00Novadis Faro22191612492202401929225917.03.202306:41:3000:03:5806:45:2817.03.202307:13:0017.03.202307:29:2800:44:0000:03:5800:27:3200:16:2800:47:58
4817.03.2023Novadis Leiria29221216.03.202320:24:5805:25:0201:50:0003:54:08FPC05:25:02########## ##########
4917.03.2023Novadis Lisboa29221516.03.202320:27:2502:04:3522:32:0007:12:08CPC02:04:35########## ##########
5017.03.2023Novadis granja29221616.03.202320:28:0102:21:5922:50:0006:54:08CPC02:21:59########## ##########
5117.03.2023Novadis Lisboa29222416.03.202320:32:4604:57:1401:30:0004:14:08BPC04:57:14########## ##########
5217.03.202308:00:00Refrige Funchal29226517.03.202308:31:1200:04:1808:35:3017.03.202312:39:4221:08:3800:04:18##########12:39:4204:08:30
5317.03.202310:00:00Dia Armazém Alverca II29228717.03.202310:35:5819:08:1017.03.202310:47:13B19:08:10 10:47:1300:11:15
5417.03.202310:00:00Recheio Torres Vedras29225517.03.202305:52:4100:11:1106:03:5217.03.202307:24:0323:40:16B00:11:11##########07:24:0301:31:22
5517.03.202311:00:00Tabacos Manroc    
5617.03.202311:00:00Vidanha - Abrunheira    
5717.03.202312:00:0029229117.03.202311:57:1100:09:1612:06:2717.03.202314:17:1017:37:4100:09:16##########14:17:1002:19:59
5817.03.202313:00:00Novadis Lisboa29229217.03.202311:59:3100:08:0412:07:3517:36:33C00:08:04########## ##########
5917.03.202313:00:00Novadis Lisboa29229417.03.202312:20:1300:09:3612:29:4917:14:19C00:09:36########## ##########
6017.03.202314:00:00Recheio Albufeira29229717.03.202313:44:3900:09:5813:54:3715:49:31F00:09:58########## ##########
6117.03.202314:40:00Magatzem Lima29229817.03.202314:37:5500:25:0815:03:0317.03.202316:40:2814:41:05C00:25:08##########16:40:2802:02:33
6217.03.202320:00:00Pingo Doce Azambuja Ambiente JIT VN29222717.03.202320:36:2500:07:4420:44:0917.03.202321:43:4108:59:5900:07:44##########21:43:4101:07:16
6317.03.202321:00:00Recheio Vila Real29222817.03.202321:29:1500:08:4421:37:5917.03.202322:40:4808:06:09B00:08:44##########22:40:4801:11:33
64    
65    
66    
67    
68    
69    
70    
71    
72    
73    
74    
75    
76    
77    
78    
mapa_cargas
Cell Formulas
RangeFormula
T2:T78T2=I2
U2:U78U2=L2-J2
V2:V78V2=N2-L2
W2:W78W2=N2-H2
 
Upvote 0
With formula:
Book1
ABCDEFGHIJKLMNOPQRST
1Data plan.registroHr.planej.registroNome de ClienteNº RemessaNº transporteYARD CAR IDData RegistoHora RegistoEsperaHora EntradaData Saída MercHora Saída MercData de SaídaHora SaídaPermanênciaVCartão MotoristaEspera
217.03.20230.541667Operador Logistico Carregado2.22E+091.95E+0929229317.03.20230.5034260.0021760.50560217.03.20230.59031317.03.20230.6077890.1021880.002176
317.03.20230Pingo Doce Distribuição Regional Su2.22E+091.95E+0929224017.03.20230.1136340.0085070.12214117.03.20230.1522817.03.20230.1584260.0362850.008507
417.03.20230.791667Pingo Doce Azambuja Ambiente JIT VN2.22E+091.95E+0929229517.03.20230.5407750.013750.55452517.03.20230.58388917.03.20230.5912850.0367590.01375
517.03.20230.791667Pingo Doce Azambuja Ambiente JIT VN2.22E+091.95E+0929230317.03.20230.7466670.0025810.74924817.03.20230.77362317.03.20230.7737730.0245250.002581
4617.03.20230.208333Novadis Castelo Branco2.22E+092202421229225217.03.20230.2282520.0045250.23277817.03.20230.28328717.03.20230.292280.059502C0.004525
4717.03.20230.25Novadis Faro2.22E+092202401929225917.03.20230.2788190.0027550.28157417.03.20230.30069417.03.20230.312130.0305560.002755
4817.03.20230Novadis Leiria29221216.03.20230.8506710.2257180.076389000.162593FPC0.225718
4917.03.20230Novadis Lisboa29221516.03.20230.8523730.0865160.938889000.300093CPC0.086516
5017.03.20230Novadis granja29221616.03.20230.8527890.09860.951389000.287593CPC0.0986
5117.03.20230Novadis Lisboa29222416.03.20230.8560880.2064120.0625000.176481BPC0.206412
5217.03.20230.333333Refrige Funchal29226517.03.20230.3550.0029860.357986017.03.20230.5275690.8809950.002986
5317.03.20230.416667Dia Armazém Alverca II29228717.03.20230.4416440.7973380017.03.20230.4494560B0.797338
84
85SumCount
86Exc0.6172454
871.754676560.031333
880.031333single formula
Sheet2
Cell Formulas
RangeFormula
T46:T53,T2:T5T2=I2
H86H86=SUMIFS(T2:T78,E2:E78,"",R2:R78,"PC")
I86I86=COUNTIFS(T2:T78,">0",E2:E78,"",R2:R78,"PC")
H87H87=SUM(T2:T78)-SUMIFS(T2:T78,E2:E78,"",R2:R78,"PC")
I87I87=COUNTIF(T2:T78,">0")-COUNTIFS(T2:T78,">0",E2:E78,"",R2:R78,"PC")
J87J87=H87/I87
J88J88=(SUM(T2:T78)-SUMIFS(T2:T78,E2:E78,"",R2:R78,"PC"))/(COUNTIF(T2:T78,">0")-COUNTIFS(T2:T78,">0",E2:E78,"",R2:R78,"PC"))

Do you actually need VBA?
Confirm the above result first.
 
Upvote 0
bebo021999 said:
With formula:
Book1
ABCDEFGHIJKLMNOPQRST
1Data plan.registroHr.planej.registroNome de ClienteNº RemessaNº transporteYARD CAR IDData RegistoHora RegistoEsperaHora EntradaData Saída MercHora Saída MercData de SaídaHora SaídaPermanênciaVCartão MotoristaEspera
217.03.20230.541667Operador Logistico Carregado2.22E+091.95E+0929229317.03.20230.5034260.0021760.50560217.03.20230.59031317.03.20230.6077890.1021880.002176
317.03.20230Pingo Doce Distribuição Regional Su2.22E+091.95E+0929224017.03.20230.1136340.0085070.12214117.03.20230.1522817.03.20230.1584260.0362850.008507
417.03.20230.791667Pingo Doce Azambuja Ambiente JIT VN2.22E+091.95E+0929229517.03.20230.5407750.013750.55452517.03.20230.58388917.03.20230.5912850.0367590.01375
517.03.20230.791667Pingo Doce Azambuja Ambiente JIT VN2.22E+091.95E+0929230317.03.20230.7466670.0025810.74924817.03.20230.77362317.03.20230.7737730.0245250.002581
4617.03.20230.208333Novadis Castelo Branco2.22E+092202421229225217.03.20230.2282520.0045250.23277817.03.20230.28328717.03.20230.292280.059502C0.004525
4717.03.20230.25Novadis Faro2.22E+092202401929225917.03.20230.2788190.0027550.28157417.03.20230.30069417.03.20230.312130.0305560.002755
4817.03.20230Novadis Leiria29221216.03.20230.8506710.2257180.076389000.162593FPC0.225718
4917.03.20230Novadis Lisboa29221516.03.20230.8523730.0865160.938889000.300093CPC0.086516
5017.03.20230Novadis granja29221616.03.20230.8527890.09860.951389000.287593CPC0.0986
5117.03.20230Novadis Lisboa29222416.03.20230.8560880.2064120.0625000.176481BPC0.206412
5217.03.20230.333333Refrige Funchal29226517.03.20230.3550.0029860.357986017.03.20230.5275690.8809950.002986
5317.03.20230.416667Dia Armazém Alverca II29228717.03.20230.4416440.7973380017.03.20230.4494560B0.797338
84
85SumCount
86Exc0.6172454
871.754676560.031333
880.031333single formula
Sheet2
Cell Formulas
RangeFormula
T46:T53,T2:T5T2=I2
H86H86=SUMIFS(T2:T78,E2:E78,"",R2:R78,"PC")
I86I86=COUNTIFS(T2:T78,">0",E2:E78,"",R2:R78,"PC")
H87H87=SUM(T2:T78)-SUMIFS(T2:T78,E2:E78,"",R2:R78,"PC")
I87I87=COUNTIF(T2:T78,">0")-COUNTIFS(T2:T78,">0",E2:E78,"",R2:R78,"PC")
J87J87=H87/I87
J88J88=(SUM(T2:T78)-SUMIFS(T2:T78,E2:E78,"",R2:R78,"PC"))/(COUNTIF(T2:T78,">0")-COUNTIFS(T2:T78,">0",E2:E78,"",R2:R78,"PC"))

Do you actually need VBA?
Confirm the above result first.
Click to expand...
thank you for the reply, but yes, i need it on VBA, I have another sheet were i'm grouping different indicators, trying to do it all in one click:

mapa.xlsm
ABCDEFGHIJKLMNOPQ
1CargasTEMPOSCAPACIDADESPICKING
2EfetuadasPlaneadas
3DomesticoPré-CargasGranjaOutrosexportaçãoDomesticoEsperaCarga/DescargaFinalizaçãoTruck residence time100200300Tarefas CriadasUnd. CriadasTarefas FeitasUnd. Feitas
446131101
5
6TEST BUTTON HERE
INDICATORS
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
188
Denny57
D
N
  • Solved
Change #DIV/0! to Blank in VBA
Replies
5
Views
129
Alex Blakenburg
A
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
220
Domenic
D
M
  • Question Question
Pivot Table Calculated Field Average and Std Deviation (control chart)
Replies
4
Views
283
Sergius
S
S
  • Question Question
How to get average of percentages in a column with filtered cells and excluding zero
Replies
2
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,814
Messages
6,181,125
Members
453,021
Latest member
Justyna P

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