Macro to count the amount of times a number is listed

[keranali]

Good Day all I have two work sheets, one contains data tab and the other an overview tab with a table to count the amount of times a number is listed per month, and 7 day week, when the month or week 1-4 is inputted. Please see example below:

Data Table

the project M.xlsm
	B	C	D
1	Date	Time	Num
2	2-Jan-23	Morning	33
3	2-Jan-23	Midday	21
4	2-Jan-23	Afternoon	4
5	2-Jan-23	Evening	1
6	3-Jan-23	Morning	2
7	3-Jan-23	Midday	15
8	3-Jan-23	Afternoon	32
9	3-Jan-23	Evening	22
10	4-Jan-23	Morning	27
11	4-Jan-23	Midday	5
12	4-Jan-23	Afternoon	32
13	4-Jan-23	Evening	35
14	5-Jan-23	Morning	18
15	5-Jan-23	Midday	12
16	5-Jan-23	Afternoon	15
17	5-Jan-23	Evening	11
18	6-Jan-23	Morning	19
19	6-Jan-23	Midday	12
20	6-Jan-23	Afternoon	2
21	6-Jan-23	Evening	20
22	7-Jan-23	Morning	36
23	7-Jan-23	Midday	36
Data

Overview tab with the count table:

the project M.xlsm
	M	N	O	P	Q	R
18	Count
19	1	1	13		25
20	2	1	14		26
21	3		15	2	27	1
22	4	1	16		28
23	5	1	17		29
24	6		18	1	30
25	7		19	1	31
26	8		20	1	32	2
27	9		21	1	33	1
28	10		22	1	34
29	11	1	23		35	1
30	12	2	24		36	2
Overview

.


Input Table:

the project M.xlsm
	A	B
1		Input
2
3	Month	Jan
4	Week	1
Overview

Whereby Jan displays the count for January and Feb For February and Week with a zero value displays the complete month, week 1 displays the first week and week 2 the second etc.

 

[Mario_R]

See if this works for you:

Test_5.xlsx
\______________/
​

Test_5.xlsx
	M	N	O	P	Q	R
18	Count
19	1		13		25
20	2		14		26
21	3		15		27
22	4		16		28
23	5		17		29
24	6		18		30
25	7		19		31
26	8		20		32
27	9		21		33
28	10		22		34
29	11		23		35
30	12		24		36
Overview

Cell Formulas
Range	Formula
R19:R30,P19:P30,N19:N30	N19	=COUNTIFS(Data!$D$1:$D$23, M19, Data!$B$1:$B$23, ">=" & $A$6, Data!$B$1:$B$23, "<=" & $B$6)

 

[Mario_R]

The formulas in the N, P and R columns of the 'Overview' sheet are incorrectly modified: that's your problem.

 

[Mario_R]

Hello.
Regarding the month of February: What range of days does Week 1 include for you?...

 

[keranali]

Good day thanks for your response February will be from the 1st to the 7th

 

[keranali]

Thanks Very much really appreciated I downloaded your workbook and it works perfect going to implement on mine and test will give more feedback. Thanks again

 

[keranali]

The formula has been integrated into my work book and functions perfect one more question I want to be able to insert 0 on the week input box and the table to display the entire month that is inputted from day 1 to the 31st. Is this possible?

 

[Mario_R]

Excellent!...

I will analyze your new requirement.

 

[Mario_R]

Let's change the formulas in cells A6 and B6:

Test_5.xlsx
	A	B
1	Input
2
3	Month	Jan
4	Week	0
5
6	1/1/23	31/1/23
Overview

Cell Formulas
Range	Formula
A6	A6	=DATE( YEAR(TODAY()), MATCH($B$3, $B$8:$B$19, 0), IF($B$4=0, 1, 7*$B$4 - 6))
B6	B6	= IF($B$4=0, EOMONTH(A6, 0), A6 + 6)

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$B$8:$B$19

 

[keranali]

Its working however Jan has no information and May has info. I am thinking Its something I am doing wrong.

the project 1.0.xlsm
	A	B
3	Month	Jan
4	Week	0
5	Number	5
6	1/1/23	31/1/23
7
8	1/1/23	Jan
9	1/2/23	Feb
10	1/3/23	Mar
11	1/4/23	Apr
12	1/5/23	May
13	1/6/23	Jun
14	1/7/23	Jul
15	1/8/23	Aug
16	1/9/23	Sep
17	1/10/23	Oct
18	1/11/23	Nov
19	1/12/23	Dec
Overview

Cell Formulas
Range	Formula
A6	A6	=DATE( YEAR(TODAY()), MATCH($B$3, $B$8:$B$19, 0), IF($B$4=0, 1, 7*$B$4 - 6))
B6	B6	= IF($B$4=0, EOMONTH(A6, 0), A6 + 6)
B8:B19	B8	=TEXT(A8, "[$-en-US]mmm")
A9:A19	A9	=EDATE(A8, 1)

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$B$8:$B$19

the project 1.0.xlsm
	M	N	O	P	Q	R
18	Count
19	1		13		25
20	2		14		26
21	3		15		27
22	4		16		28
23	5		17		29
24	6		18		30
25	7		19		31
26	8		20		32
27	9		21		33
28	10		22		34
29	11		23		35
30	12		24		36
Overview

Cell Formulas
Range	Formula
N19:N30	N19	=COUNTIFS(Data!$D$1:$D$5000, M19,Data!$B$1:$B$5000, ">=" & Data!$I$2, Data!$B$1:$B$5000, "<=" & Data!$J$2)
P19:P30,R19:R30	P19	=COUNTIFS(Data!$D$1:$D$5000, O19, Data!$B$1:$B$5000, ">=" & Data!$I$2, Data!$B$1:$B$5000, "<=" & Data!$J$2)

the project 1.0.xlsm
	A	B
3	Month	May
4	Week	0
5	Number	5
6	1/5/23	31/5/23
7
8	1/1/23	Jan
9	1/2/23	Feb
10	1/3/23	Mar
11	1/4/23	Apr
12	1/5/23	May
13	1/6/23	Jun
14	1/7/23	Jul
15	1/8/23	Aug
16	1/9/23	Sep
17	1/10/23	Oct
18	1/11/23	Nov
19	1/12/23	Dec
Overview

Cell Formulas
Range	Formula
A6	A6	=DATE( YEAR(TODAY()), MATCH($B$3, $B$8:$B$19, 0), IF($B$4=0, 1, 7*$B$4 - 6))
B6	B6	= IF($B$4=0, EOMONTH(A6, 0), A6 + 6)
B8:B19	B8	=TEXT(A8, "[$-en-US]mmm")
A9:A19	A9	=EDATE(A8, 1)

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$B$8:$B$19

the project 1.0.xlsm
	M	N	O	P	Q	R
18	Count
19	1		13		25	1
20	2		14	1	26	1
21	3		15	1	27	1
22	4		16	1	28
23	5		17	1	29
24	6		18	1	30
25	7		19		31
26	8		20		32
27	9		21		33
28	10	1	22		34
29	11		23		35	1
30	12		24		36	1
Overview

Cell Formulas
Range	Formula
N19:N30	N19	=COUNTIFS(Data!$D$1:$D$5000, M19,Data!$B$1:$B$5000, ">=" & Data!$I$2, Data!$B$1:$B$5000, "<=" & Data!$J$2)
P19:P30,R19:R30	P19	=COUNTIFS(Data!$D$1:$D$5000, O19, Data!$B$1:$B$5000, ">=" & Data!$I$2, Data!$B$1:$B$5000, "<=" & Data!$J$2)