Count the number of entries per month during the year

[philb99]

I have member information on a line with a date recorded - I am trying to count the number of entries for each month, ie 2 in Jan, 3 in Feb etc

If some one could help please

Dropbox

www.dropbox.com

 

[hagia_sofia]

Hello, maybe something like for January and so on:

=SUMPRODUCT(--(MONTH(A2:A26)=1))

 

[philb99]

Hello, maybe something like for January and so on:

=SUMPRODUCT(--(MONTH(A2:A26)=1))

Many thanks, I presume that I would to do the formula 12 times.

Do you know of any way the information could all filter into the respective months at once

 

[hagia_sofia]

Any chance you have a newer version of Excel than 2013?

 

[Sufiyan97]

You can use a Pivot table like this,

Move date to Rows field and it will automatically group to Year, Month and Quarters, just keep months in Rows field and remove others, then again move month to Value field

drop box query5.xlsx
	A	B	C	D	E	F	G
1	Date
2	1/2/2024			Months (Date)	Count of Months (Date)
3	1/26/2024			Jan	2
4	3/3/2024			Mar	4
5	3/4/2024			Apr	3
6	3/14/2024			May	2
7	3/29/2024			Jun	4
8	4/5/2024			Jul	3
9	4/6/2024			Aug	2
10	4/28/2024			Sep	2
11	5/13/2024			Oct	1
12	5/29/2024			Dec	2
13	6/3/2024			Grand Total	25
14	6/4/2024
15	6/13/2024
16	6/29/2024
17	7/5/2024
18	7/14/2024
19	7/29/2024
20	8/14/2024
21	8/29/2024
22	9/14/2024
23	9/29/2024
24	10/15/2024
25	12/1/2024
26	12/2/2024
27
Sheet1

 

[philb99]

Any chance you have a newer version of Excel than 2013?

No Sorry

 

[philb99]

You can use a Pivot table like this,

Move date to Rows field and it will automatically group to Year, Month and Quarters, just keep months in Rows field and remove others, then again move month to Value field

drop box query5.xlsx
	A	B	C	D	E	F	G
1	Date
2	1/2/2024			Months (Date)	Count of Months (Date)
3	1/26/2024			Jan	2
4	3/3/2024			Mar	4
5	3/4/2024			Apr	3
6	3/14/2024			May	2
7	3/29/2024			Jun	4
8	4/5/2024			Jul	3
9	4/6/2024			Aug	2
10	4/28/2024			Sep	2
11	5/13/2024			Oct	1
12	5/29/2024			Dec	2
13	6/3/2024			Grand Total	25
14	6/4/2024
15	6/13/2024
16	6/29/2024
17	7/5/2024
18	7/14/2024
19	7/29/2024
20	8/14/2024
21	8/29/2024
22	9/14/2024
23	9/29/2024
24	10/15/2024
25	12/1/2024
26	12/2/2024
27
Sheet1

View attachment 120312

Sorry - where have the months come from, when I put Dates in Rows, I just the list of days = 1 with a Total = 25

 

[Sufiyan97]

If your dates are not grouping then go to PivotTable Analyze and go to group field

Then select Months

 

[Rick Rothstein]

Many thanks, I presume that I would to do the formula 12 times.

Do you know of any way the information could all filter into the respective months at once

You can use this formula and copy it down 12 times...
=SUMPRODUCT(0+(MONTH(A$2:A$15)=ROWS(A$2:A2)))

 

[alansidman]

Here's an alternative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Month Name" = Table.TransformColumns(Source, {{"Date", each Date.MonthName(_), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Date"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

drop box query5.xlsx
	A	B	C	D
1	Date		Date	Count
2	1/2/2024		January	2
3	1/26/2024		March	4
4	3/3/2024		April	3
5	3/4/2024		May	2
6	3/14/2024		June	4
7	3/29/2024		July	3
8	4/5/2024		August	2
9	4/6/2024		September	2
10	4/28/2024		October	1
11	5/13/2024		December	2
12	5/29/2024
13	6/3/2024
14	6/4/2024
15	6/13/2024
16	6/29/2024
17	7/5/2024
18	7/14/2024
19	7/29/2024
20	8/14/2024
21	8/29/2024
22	9/14/2024
23	9/29/2024
24	10/15/2024
25	12/1/2024
26	12/2/2024
Sheet1