formula to count 2 weeks

[Lukma]

Hi guys

Can anyone assist with COUNTIFS Formula based on multiple criteria and count on every 2 weeks

In Column O23 i need to count how many of D is entered for every 2 weeks
In Column O23 i need to count how many of P is entered for every 2 weeks
i Have my WeekNum in Column K23

Appreciate with a formula that can do the counting in every 2 weeks

Weekly Planning Supply Chain Performance 2021.xlsx
	K	L	M	N	O
23	Week No's	Monthly Mus End Date	Mus-No	Supply Vessel	D/P VSL
24	53	01-Jan-21	3705	ADNOC-810	P
25	53	01-Dec-20	3706	ADNOC-224	D
26		01-Jan-21	3666-O	ASL SWIFT	D
27	53	01-Jan-21	3707	Z-POWER	D
28	1	01-Jan-21	3708	SMIT LUZON	D
29	53	01-Jan-21	3709	SMIT LUMUT	D
30	1	01-Jan-21	3710	B-LIBERTY-313	D
31	1	01-Jan-21	3711	MAC PHOENIX	D
32	1	01-Jan-21	3712	ADNOC-812	P
33	1	01-Jan-21	3713	A-HERCULES	P
34	1	01-Jan-21	1	LCT-TARFFAH	P
35	1	01-Jan-21	2	ADNOC-850	P
36	1	01-Jan-21	3	ADNOC-510	D
37	1	01-Jan-21	4	A-GRACE	D
38	1	01-Jan-21	5	MARCAP-2	P
39	1	01-Jan-21	6	QMS DELTA	D
40	1	01-Jan-21	7	ADNOC-1011	D
41	1	01-Jan-21	8	QMS NEPTUNE	D
42	1	01-Jan-21	9	ADNOC-221	D
43	1	01-Jan-21	10	SEACOR ALPS	D
44	1	01-Jan-21	11	ADNOC-229	D
45	1	01-Jan-21	12	LCT-MARWAH-1	P
46	1	01-Jan-21	13	Z-OCEAN	P
47	1	01-Jan-21	14	ADNOC-230	D
48	1	01-Jan-21	15	A-LIBERTY	D
49	1	01-Jan-21	16	ADNOC-851	P
50	1	01-Jan-21	17	A-CHLOE	D
51	1	01-Jan-21	18	ADNOC-225	D
52	1	01-Jan-21	19	ADNOC-811	P
53	1	01-Jan-21	20	SWISSCO RUBY	P
54	1	01-Jan-21	21	ADNOC-512	D
55	1	01-Jan-21	22	AMS-RUBY	D
56	1	01-Jan-21	23	ADNOC-511	D
57	1	01-Jan-21	24	ADNOC-810	P
58	1	01-Jan-21	25	ADNOC-812	P
59	1	01-Jan-21	26	MAC PHOENIX	D
60	1	01-Jan-21	27	QMS MARIMBA	D
61	1	01-Jan-21	28	ADNOC-227	D
62	1	01-Jan-21	29	LCT-WARDEH	D
63	2	01-Jan-21	30	Z-STAR	D
64	2	01-Jan-21	31	B-HOMERE	D
65	2	01-Jan-21	32	ADNOC-226	D
66	2	01-Jan-21	33	Z-POWER	D
67	2	01-Jan-21	34	ADNOC-223	D
68	2	01-Jan-21	35	SMIT LUMUT	D
69	2	01-Jan-21	36	A-HERCULES	P
70	2	01-Jan-21	37	LCT-TARFFAH	P
71	2	01-Jan-21	38	SEACOSCO OHIO	D
72	2	01-Jan-21	39	ADNOC-224	D
73	2	01-Jan-21	40	A-LIBERTY	D
74	2	01-Jan-21	41	M-SUPPORTER	D
75	2	01-Jan-21	42	Z-OCEAN	P
76	2	01-Jan-21	43	ADNOC-510	D
77	2	01-Jan-21	44	LCT-MARWAH-1	P
78	2	01-Jan-21	45	MUBARAK SPIRIT	D
79	2	01-Jan-21	46	ADNOC-512	D
80	2	01-Jan-21	47	A-CHLOE	D
81	2	01-Jan-21	48	ADNOC-811	P
Vessels Activity Report

 

[jasonb75]

Something like this?

It will probably give some erroneous results when thereis week 53 data for the beginning and end of the same year, in which case it would need to be done on date instead of week number. I'm not going to attempt a formula to do that without a better explanation of how the weeks should be paired.

Book1
	Q	R	S	T
23	From week	To Week	D	P
24	53	1	25	13
25	2	3	14	5
26	4	5	0	0
27	6	7	0	0
28	8	9	0	0
Sheet2

Cell Formulas
Range	Formula
S24:T28	S24	=SUM(COUNTIFS($K$24:$K$81,$Q24:$R24,$O$24:$O$81,S$23))
Q25:Q28	Q25	=R24+1
R25:R28	R25	=Q25+1
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Lukma]

Hi Jason
Thanks with the reply, what I need is to count the D in column O
Let say I replace my weeknum with a date and time entry example 1/10/2021 10:00 and next row is 2/10/21 14:00 and so on, how can I count how many times D appear in column O for every two weeks in a month

My manager want me to use a formula to count the numbers of time D appear for every two weeks in a month that is 14 days
I will appreciate so I can change my week number to date

 

[jasonb75]

That still doesn't tell me how the weeks should be paired.

What is the criteria for identifying the beginning and end of a 2 week period? More specifically, how would you decide which are the correct dates / week numbers for the first 2 week period of the year?

 

[Lukma]

Jason thanks so much
But is there a way I could do this by date entry
The first criteria range is column O and am looking for the how many times D occured in every two weeks
Please advise me how to derive on this and if there is better way to count it in every 14 days of the month
Thanks

 

[jasonb75]

If you don't answer the question that I asked then I can't help you!! I can write formulas, I can not read minds!

How do you decide where the first 2 weeks of the year should start and end??