Formula to calculate the Average late deliver monthly and the Percentage

[Lukma]

Hi Guy

please can anyone help out with this i was trying to calculate the Average of Late from Column EO and also find the Percentage, and also to sumifs monthly the total hours of Late and Ontime
So i Create a Column in from
Column EJ9 is the Month Dropdown
Column EK is Criteria List Name for Late and On Time

Appreciate if anyone could assist with this

Regards

ILSP KPI Performance Tracking Master Data-1.xlsx
	EJ	EK	EL	EM	EN	EO	EP
9	01-May-21		Suppliers
10	Total Hours	Late	1677:46
11	Average Of	Late	13:58
12	Percentage	Late	58%
13	Total Hours	On Time
14	Average Of	On Time
15	Percentage	On Time
16
17	Month	Mus No Vsl	VSL Start Jetty Date & Time	Supplier Name	Arrival Date & Time	Delay Hours	Later On Time
18	01-May-21	1149	13/05/21 08:30	Schlumberger	13/05/21 09:10	0:40	Late
19	01-May-21	1149	13/05/21 08:30	Schlumberger	13/05/21 11:10	2:40	Late
20	01-May-21	1149	13/05/21 08:30	Uni Arab	13/05/21 18:30	10:00	Late
21	01-May-21	1149	13/05/21 08:30	Emdad	13/05/21 17:00	8:30	Late
22	01-May-21	1149	13/05/21 08:30	WeatherFord	12/05/21 16:45	-15:45	On TIme
23	01-May-21	1120	10/05/21 21:45	United Safety	09/05/21 11:00	-10:45	On TIme
24	01-May-21	1123	10/05/21 22:30	Alphamed	09/05/21 09:21	-13:9	On TIme
25	01-May-21	1125	11/05/21 06:36	Halliburton	11/05/21 10:45	4:09	Late
26	01-May-21	1125	11/05/21 06:36	Schlumberger	11/05/21 09:00	2:24	Late
27	01-May-21	1125	11/05/21 06:36	Al Ahlia	10/05/21 11:00	-19:36	On TIme
28	01-May-21	1125	11/05/21 06:36	Alphamed	10/05/21 09:00	-21:36	On TIme
29	01-May-21	1126	11/05/21 07:00	Halliburton	10/05/21 12:00	-19:0	On TIme
30	01-May-21	1126	11/05/21 07:00	Halliburton	09/05/21 12:20	-18:40	On TIme
31	01-May-21	1126	11/05/21 07:00	Averda	10/05/21 09:40	-21:20	On TIme
32	01-May-21	1126	11/05/21 07:00	WeatherFord	10/05/21 16:00	-15:0	On TIme
33	01-May-21	1127	11/05/21 14:20	WeatherFord	10/05/21 14:30	-23:50	On TIme
34	01-May-21	1127	11/05/21 14:20	Al Ahlia	10/05/21 12:40	-1:40	On TIme
35	01-May-21	1127	11/05/21 14:20	BJ Service	10/05/21 22:25	-15:55	On TIme
36	01-May-21	1127	11/05/21 14:20	Sodexo / Kelvin	11/05/21 16:10	1:50	Late
37	01-May-21	1127	11/05/21 14:20	Selective Marine	11/05/21 20:10	5:50	Late
38	01-May-21	1127	11/05/21 14:20	Alphamed	10/05/21 09:00	-5:20	On TIme
39	01-May-21	1127	11/05/21 14:20	Stardford Marine	11/05/21 20:55	6:35	Late
40	01-May-21	1130	11/05/21 22:00	AmBhertel LLc	11/05/21 20:10	-1:50	On TIme
41	01-May-21	1132	12/05/21 00:18	Al Ghaith	11/05/21 09:30	-14:48	On TIme
42	01-May-21	1135	11/05/21 16:40	International Tubular	11/05/21 21:50	5:10	Late
43	01-May-21	1135	11/05/21 16:40	Al Ahlia	11/05/21 16:45	0:05	Late
44	01-May-21	1136	12/05/21 01:55	WeatherFord	09/05/21 16:45	-9:10	On TIme
45	01-May-21	1136	12/05/21 01:55	Alphamed	11/05/21 09:30	-16:25	On TIme
46	01-May-21	1139	12/05/21 03:05	ADNH	11/05/21 12:50	-14:15	On TIme
47	01-May-21	1137	12/05/21 02:10	Al Mansoori	10/05/21 14:00	-12:10	On TIme
48	01-May-21	1137	12/05/21 02:10	Alphamed	11/05/21 00:20	-1:50	On TIme
49	01-May-21	1137	12/05/21 02:10	Halliburton	10/05/21 13:15	-12:55	On TIme
50	01-May-21	1137	12/05/21 02:10	Al Ghaith	10/05/21 11:30	-14:40	On TIme
51	01-May-21	1137	12/05/21 02:10	Scomi	11/05/21 12:00	-14:10	On TIme
52	01-May-21	1140	12/05/21 10:45	WeatherFord	10/05/21 14:30	-20:15	On TIme
ILSP_KPI_Tracking_Data_Entry

Cell Formulas
Range	Formula
EL10	EL10	=SUMIFS($EO$18:$EO$60000,$EJ$18:$EJ$60000,$EJ$9,$EP$18:$EP$60000,$EK$10)
EL11	EL11	=AVERAGEIFS(EO18:EO60000,EJ18:EJ60000,EJ9,EO18:EO60000,">0",EP18:EP60000,EK10)
EL12	EL12	=EL11
EO18:EO52	EO18	=IFERROR(IF(EN18-EL18<0, "-" & TEXT(ABS(EN18-EL18),"h:m"), EN18-EL18),"")
EP18:EP52	EP18	=IF(EN18>EL18,"Late",IF(EN18<EL18,"On TIme",""))
EJ18:EJ52	EJ18	=IFERROR(EOMONTH(EL18,-1)+1,"")
EL18:EL52	EL18	=IFERROR(VLOOKUP(EK18,$DL$18:$DW$60002,10,0),"")

Cells with Data Validation
Cell	Allow	Criteria
EJ9	List	=$ACT$18:$ACT$209

 

[johnny51981]

Hi @Lukma
Because your cells calculating your Late Total Hours, Average Of, and Percentage by including the formula pointing to the word "Late" in EK10 through EK12, respectively, you can simply copy and paste these 3 formulas in EL10 through EL12 and Paste Special Formulas into EL13 through EL15, and it will then be pointing to the words "On Time". HOWEVER, make sure to not make EK10 through EK12 are not absolute rows.

Example:
EL10 =SUMIFS($EO$18:$EO$60000,$EJ$18:$EJ$60000,$EJ$9,$EP$18:$EP$60000,$EK10) I removed the $ between EK and 10.
EL13 =SUMIFS($EO$18:$EO$60000,$EJ$18:$EJ$60000,$EJ$9,$EP$18:$EP$60000,$EK13) After removing the absolute from above, your copy and Paste will update accordingly and results received will be for "On Time"