Macro to count the amount of times a number is listed

keranali

Rules Violation
Joined
Oct 4, 2010
Messages
234
Office Version
  1. 365
Platform
  1. Windows
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
BCD
1DateTimeNum
22-Jan-23Morning33
32-Jan-23Midday21
42-Jan-23Afternoon4
52-Jan-23Evening1
63-Jan-23Morning2
73-Jan-23Midday15
83-Jan-23Afternoon32
93-Jan-23Evening22
104-Jan-23Morning27
114-Jan-23Midday5
124-Jan-23Afternoon32
134-Jan-23Evening35
145-Jan-23Morning18
155-Jan-23Midday12
165-Jan-23Afternoon15
175-Jan-23Evening11
186-Jan-23Morning19
196-Jan-23Midday12
206-Jan-23Afternoon2
216-Jan-23Evening20
227-Jan-23Morning36
237-Jan-23Midday36
Data


Overview tab with the count table:


the project M.xlsm
MNOPQR
18Count
19111325
20211426
213152271
22411628
23511729
24618130
25719131
268201322
279211331
281022134
2911123351
3012224362
Overview
.


Input Table:

the project M.xlsm
AB
1Input
2
3MonthJan
4Week1
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hello.
Regarding the month of February: What range of days does Week 1 include for you?...
 
Upvote 0
Good day thanks for your response February will be from the 1st to the 7th
 
Upvote 0
Upvote 1
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
 
Upvote 0
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?
 
Upvote 0
Excellent!...

I will analyze your new requirement.
 
Upvote 0
Let's change the formulas in cells A6 and B6:

Test_5.xlsx
AB
1Input
2
3MonthJan
4Week0
5
61/1/2331/1/23
Overview
Cell Formulas
RangeFormula
A6A6=DATE( YEAR(TODAY()), MATCH($B$3, $B$8:$B$19, 0), IF($B$4=0, 1, 7*$B$4 - 6))
B6B6= IF($B$4=0, EOMONTH(A6, 0), A6 + 6)
Cells with Data Validation
CellAllowCriteria
B3List=$B$8:$B$19
 
Upvote 0
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
AB
3MonthJan
4Week0
5Number5
61/1/2331/1/23
7
81/1/23Jan
91/2/23Feb
101/3/23Mar
111/4/23Apr
121/5/23May
131/6/23Jun
141/7/23Jul
151/8/23Aug
161/9/23Sep
171/10/23Oct
181/11/23Nov
191/12/23Dec
Overview
Cell Formulas
RangeFormula
A6A6=DATE( YEAR(TODAY()), MATCH($B$3, $B$8:$B$19, 0), IF($B$4=0, 1, 7*$B$4 - 6))
B6B6= IF($B$4=0, EOMONTH(A6, 0), A6 + 6)
B8:B19B8=TEXT(A8, "[$-en-US]mmm")
A9:A19A9=EDATE(A8, 1)
Cells with Data Validation
CellAllowCriteria
B3List=$B$8:$B$19


the project 1.0.xlsm
MNOPQR
18Count
191 13 25 
202 14 26 
213 15 27 
224 16 28 
235 17 29 
246 18 30 
257 19 31 
268 20 32 
279 21 33 
2810 22 34 
2911 23 35 
3012 24 36 
Overview
Cell Formulas
RangeFormula
N19:N30N19=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:R30P19=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
AB
3MonthMay
4Week0
5Number5
61/5/2331/5/23
7
81/1/23Jan
91/2/23Feb
101/3/23Mar
111/4/23Apr
121/5/23May
131/6/23Jun
141/7/23Jul
151/8/23Aug
161/9/23Sep
171/10/23Oct
181/11/23Nov
191/12/23Dec
Overview
Cell Formulas
RangeFormula
A6A6=DATE( YEAR(TODAY()), MATCH($B$3, $B$8:$B$19, 0), IF($B$4=0, 1, 7*$B$4 - 6))
B6B6= IF($B$4=0, EOMONTH(A6, 0), A6 + 6)
B8:B19B8=TEXT(A8, "[$-en-US]mmm")
A9:A19A9=EDATE(A8, 1)
Cells with Data Validation
CellAllowCriteria
B3List=$B$8:$B$19


the project 1.0.xlsm
MNOPQR
18Count
191 13 251
202 141261
213 151271
224 16128 
235 17129 
246 18130 
257 19 31 
268 20 32 
279 21 33 
2810122 34 
2911 23 351
3012 24 361
Overview
Cell Formulas
RangeFormula
N19:N30N19=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:R30P19=COUNTIFS(Data!$D$1:$D$5000, O19, Data!$B$1:$B$5000, ">=" & Data!$I$2, Data!$B$1:$B$5000, "<=" & Data!$J$2)
 
Upvote 0
My Apologies I had some cells mixed up I seems fine now I will test and Report back thanks so much this is brilliant:)
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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