Percentage by Month Formula Help

[GW81]

Hi,

I'm working on an SLA performance report for a distribution centre. I have daily data that is pasted into a master spreadsheet that calculates whether each order has shipped within SLA.

I need to add a summary page that shows the percentage of orders for each month that shipped on time. I can work out the formula I that looks at the data as a whole but then only calculates the result by a specific month.

I've attached a sample. Each row is an individual order, column is the SLA performance for that order and column I is the month.

Many Thanks in Advance!

sample data 2 (1).xlsx
	A	B	C	D	E	F	G	H	I
1	Created Date	Sent Date	A	wk/day	wk/day	SLA	Created Date	Sent Date	Month
2	11/4/21 10:52	11/4/21 15:00	On Time	4	4	On Time	Thu	Thu	Nov
3	11/4/21 9:46	11/4/21 15:00	On Time	4	4	On Time	Thu	Thu	Nov
4	11/4/21 11:58	11/4/21 14:45	On Time	4	4	On Time	Thu	Thu	Nov
5	11/3/21 14:42	11/4/21 10:15	On Time	3	4	On Time	Wed	Thu	Nov
6	11/4/21 4:27	11/4/21 10:00	On Time	4	4	On Time	Thu	Thu	Nov
7	11/4/21 5:23	11/4/21 10:00	On Time	4	4	On Time	Thu	Thu	Nov
8	11/4/21 4:27	11/4/21 10:00	On Time	4	4	On Time	Thu	Thu	Nov
9	11/3/21 14:42	11/4/21 10:00	On Time	3	4	On Time	Wed	Thu	Nov
10	11/3/21 14:53	11/4/21 9:45	On Time	3	4	On Time	Wed	Thu	Nov
11	11/3/21 18:31	11/4/21 9:45	On Time	3	4	On Time	Wed	Thu	Nov
12	11/3/21 18:42	11/4/21 9:45	On Time	3	4	On Time	Wed	Thu	Nov
13	11/4/21 4:27	11/4/21 9:45	On Time	4	4	On Time	Thu	Thu	Nov
14	11/4/21 4:27	11/4/21 9:45	On Time	4	4	On Time	Thu	Thu	Nov
15	11/4/21 4:27	11/4/21 9:45	On Time	4	4	On Time	Thu	Thu	Nov
16	11/4/21 4:27	11/4/21 9:45	On Time	4	4	On Time	Thu	Thu	Nov
17	11/3/21 21:12	11/4/21 9:45	On Time	3	4	On Time	Wed	Thu	Nov
18	11/3/21 15:51	11/4/21 9:45	On Time	3	4	On Time	Wed	Thu	Nov
19	11/4/21 4:27	11/4/21 9:45	On Time	4	4	On Time	Thu	Thu	Nov
20	11/3/21 15:54	11/4/21 9:45	On Time	3	4	On Time	Wed	Thu	Nov
21	11/4/21 4:27	11/4/21 9:30	On Time	4	4	On Time	Thu	Thu	Nov
22	11/3/21 23:36	11/4/21 9:30	On Time	3	4	On Time	Wed	Thu	Nov
23	11/4/21 4:27	11/4/21 9:30	On Time	4	4	On Time	Thu	Thu	Nov
24	11/4/21 4:26	11/4/21 9:30	On Time	4	4	On Time	Thu	Thu	Nov
25	11/3/21 7:23	11/3/21 15:15	On Time	3	3	On Time	Wed	Wed	Nov
26	11/3/21 8:54	11/3/21 15:15	On Time	3	3	On Time	Wed	Wed	Nov
27	11/3/21 4:25	11/3/21 10:30	On Time	3	3	On Time	Wed	Wed	Nov
28	11/3/21 4:25	11/3/21 10:30	On Time	3	3	On Time	Wed	Wed	Nov
29	11/3/21 4:25	11/3/21 10:30	On Time	3	3	On Time	Wed	Wed	Nov
30	11/2/21 14:34	11/3/21 10:15	On Time	2	3	On Time	Tue	Wed	Nov
31	11/2/21 16:25	11/3/21 10:15	On Time	2	3	On Time	Tue	Wed	Nov
32	11/3/21 4:24	11/3/21 10:15	On Time	3	3	On Time	Wed	Wed	Nov
33	11/3/21 4:25	11/3/21 10:15	On Time	3	3	On Time	Wed	Wed	Nov
34	11/3/21 4:25	11/3/21 10:15	On Time	3	3	On Time	Wed	Wed	Nov
35	11/3/21 4:25	11/3/21 10:15	On Time	3	3	On Time	Wed	Wed	Nov
36	11/3/21 5:06	11/3/21 10:15	On Time	3	3	On Time	Wed	Wed	Nov
37	11/1/21 10:29	11/2/21 16:00	Late	1	2	Late	Mon	Tue	Nov
38	11/1/21 4:26	11/2/21 16:00	Late	1	2	Late	Mon	Tue	Nov
39	10/30/21 12:55	11/2/21 16:00	Late	6	2	Late	Sat	Tue	Nov
40	10/30/21 4:04	11/2/21 16:00	Late	6	2	Late	Sat	Tue	Nov
41	11/2/21 6:43	11/2/21 15:45	On Time	2	2	On Time	Tue	Tue	Nov
42	11/2/21 9:53	11/2/21 15:45	On Time	2	2	On Time	Tue	Tue	Nov
43	11/1/21 12:45	11/2/21 15:45	On Time	1	2	On Time	Mon	Tue	Nov
44	10/31/21 10:44	11/1/21 14:45	Late	7	1	On Time	Sun	Mon	Nov
45	11/1/21 4:11	11/2/21 14:45	Late	1	2	Late	Mon	Tue	Nov
46	11/1/21 4:11	11/2/21 14:45	Late	1	2	Late	Mon	Tue	Nov
47	10/31/21 14:12	11/2/21 14:45	Late	7	2	Late	Sun	Tue	Nov
48	11/1/21 4:10	11/2/21 14:45	Late	1	2	Late	Mon	Tue	Nov
49	10/30/21 11:55	11/1/21 14:45	Late	6	1	On Time	Sat	Mon	Nov
50	10/31/21 6:09	11/2/21 14:45	Late	7	2	Late	Sun	Tue	Nov
51	10/31/21 16:58	11/2/21 14:45	Late	7	2	Late	Sun	Tue	Nov
52	11/1/21 13:25	11/2/21 13:15	On Time	1	2	On Time	Mon	Tue	Nov
53	11/1/21 14:24	11/2/21 13:00	On Time	1	2	On Time	Mon	Tue	Nov
54	11/1/21 8:45	11/2/21 13:00	Late	1	2	Late	Mon	Tue	Nov
55	11/1/21 4:11	11/2/21 13:00	Late	1	2	Late	Mon	Tue	Nov
56	11/1/21 4:11	11/2/21 13:00	Late	1	2	Late	Mon	Tue	Nov
57	11/1/21 5:46	11/2/21 13:00	Late	1	2	Late	Mon	Tue	Nov
58	10/27/21 9:14	10/27/21 14:00	On Time	3	3	On Time	Wed	Wed	Oct
59	10/22/21 8:35	10/22/21 17:15	On Time	5	5	Late	Fri	Fri	Oct
sample data 2

Cell Formulas
Range	Formula
C2:C59	C2	=IF(OR(AND(MOD($A2,1)<=0.5,INT($A2)=INT($B2)),AND(MOD($A2,1)>0.5,INT($B2)<=INT($A2)+1)),"On Time","Late")
D2:E59	D2	=WEEKDAY(A2,2)
F2:F59	F2	=IF(MOD($A2,1)<=0.5,IF(OR(AND(WEEKDAY(A2)<=5,INT($A2)=INT($B2)),WEEKDAY(B2,2)=1),"On Time","Late"),IF(OR(AND(WEEKDAY(A2)<=5,INT($B2)<=INT($A2)+1),WEEKDAY(B2,2)=1),"On Time","Late"))
G2:H59	G2	=INT(A2)
I2:I59	I2	=TEXT(B2,"mmm")

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS(F:F,"on time",I:I,"Nov")/COUNTIFS(I:I,"Nov")

 

[GW81]

Thank you!

So simple when you see it written out!

Thanks again

 

[Fluff]

You're welcome & thanks for the feedback.