COUNTIFS using MONTH and YEAR references

redhots4 · Wednesday at 4:49 PM

I have a long list of dates. In another tab, I have a list of Month / Year combos (1/1/24 displayed as Jan-24, 2/1/24 displayed as Feb-24, etc) ranging from Jan-22 to Mar-25. I want a formula that will look at the list of dates and return the count of dates that fall within each month. See attached for a sample of the data. Thanks in advance!

Case Load 20250309_SHAWN VER.xlsx

A

B

C

D

E

1

Date Received

Month

Count

2

1/16/24

Jan-24

3

<-- Count the number of dates in colum A that are in Jan 2024

3

1/17/24

Feb-24

1

4

1/18/24

Mar-24

12

5

2/25/24

Apr-24

8

6

3/10/24

May-24

7

3/21/24

Jun-24

10

8

3/29/24

Jul-24

13

9

4/3/24

Aug-24

5

10

4/6/24

Sep-24

20

11

4/23/24

Oct-24

17

12

6/5/24

Nov-24

15

13

6/10/24

Dec-24

14

6/13/24

Jan-25

11

15

6/14/24

Feb-25

22

16

7/10/24

17

7/10/24

18

8/8/24

19

8/12/24

20

8/12/24

21

8/23/24

22

9/10/24

23

9/15/24

24

10/8/24

25

10/16/24

26

11/10/24

27

11/15/24

28

11/16/24

29

11/21/24

30

11/28/24

31

12/15/24

32

12/22/24

33

1/7/25

34

1/20/25

35

1/26/25

Sheet1

Joe4 · Wednesday at 4:52 PM

Not sure if column C is entered as text, or as a date with a custom format. But the typical way is to count the numbers between the first and last days of the month.
See here: Count cells between dates

Joe4 · Wednesday at 4:59 PM

Note that when I copy paste your data into my Excel, it shows that the value entered in cell C2 are formatted as "dd-mmm".
So, if I look in the formula bar, it shows me the entry is actually for the date 1/24/2025, not for 2024.

If you click on cell C2, and look in the formula bar, what does it show you?
What if you temporarily change the format of that cell to mm/dd/yyyy? What does that show you?

Note that all valid dates in Excel must have a month, day, and year component, though you can custom format it to just show you the pieces you want to see.
But Excel calculations run off the REAL underlying values, not the formatted ones.

redhots4 · Wednesday at 5:09 PM

I understand the date formatting. I want a formula that will look at the dates in column A and count the number that have the same month and year as the date shown in column C.

Joe4 · Wednesday at 5:19 PM

I understand exactly what you are trying to do. I am trying to get you to evaluate exactly what is in column C, if it is really a date or text.
And even if it is really a date, it may not be the date that you think it is!

It is vital to be fully aware of what we are working with so we can come up with a solution that will work for you.
So please tell us the answers to the questions I asked in post 3.

Alex Blakenburg · Wednesday at 6:42 PM

Assuming they are all valid dates, try in D3

Excel Formula:

=COUNTIFS($A$2:$A$10000, ">=" & $C2, $A$2:$A$10000, "<=" & EOMONTH($C2,0))

Joe4 · Wednesday at 7:12 PM

I originally missed the sentence above your sample data where you said:

(1/1/24 displayed as Jan-24, 2/1/24 displayed as Feb-24, etc)

and when I copied the data from your post to my spreadsheet, it made the dates 1/24/25, 2/24/25, 3/24/25, etc.
I wonder sure if it is a copy issue, or that is how your date really is entered. I am guessing a copy issue, based on what you said in your first post.

Sorry I missed that.
If that is indeed the case, Alex's formula should do what you want.

Dave Patton · Thursday at 9:52 PM

T202503a.xlsm

A

B

E

F

1

Date Received

Month

Count

2

16-Jan-24

Jan-24

3

17-Jan-24

Feb-24

1

4

18-Jan-24

Mar-24

3

5

25-Feb-24

Apr-24

3

6

10-Mar-24

Jun-24

4

7

21-Mar-24

Jul-24

2

8

29-Mar-24

Aug-24

4

9

3-Apr-24

Sep-24

2

10

6-Apr-24

Oct-24

2

11

23-Apr-24

Nov-24

5

12

5-Jun-24

Dec-24

2

13

10-Jun-24

Jan-25

3

14

13-Jun-24

15

14-Jun-24

16

10-Jul-24

17

10-Jul-24

18

8-Aug-24

19

12-Aug-24

20

12-Aug-24

21

23-Aug-24

22

10-Sep-24

23

15-Sep-24

24

8-Oct-24

25

16-Oct-24

26

10-Nov-24

27

15-Nov-24

28

16-Nov-24

29

21-Nov-24

30

28-Nov-24

31

15-Dec-24

32

22-Dec-24

33

7-Jan-25

34

20-Jan-25

35

26-Jan-25

2g

Cell Formulas
Range	Formula
E2:F13	E2	=LET(a,A1:A35-DAY(A1:A35)+1,GROUPBY(a,a,COUNT,1,0))
Dynamic array formulas.

With Excel 365 that has TrimRange, try the second example with you full range of data.
Custom format the Month Year to your preference.

T202503a.xlsm

A

B

H

I

1

Date Received

Month

Count

2

16-Jan-24

1-Jan-24

3

17-Jan-24

1-Feb-24

1

4

18-Jan-24

1-Mar-24

3

5

25-Feb-24

1-Apr-24

3

6

10-Mar-24

1-Jun-24

4

7

21-Mar-24

1-Jul-24

2

8

29-Mar-24

1-Aug-24

4

9

3-Apr-24

1-Sep-24

2

10

6-Apr-24

1-Oct-24

2

11

23-Apr-24

1-Nov-24

5

12

5-Jun-24

1-Dec-24

2

13

10-Jun-24

1-Jan-25

3

14

13-Jun-24

15

14-Jun-24

16

10-Jul-24

17

10-Jul-24

18

8-Aug-24

19

12-Aug-24

20

12-Aug-24

21

23-Aug-24

22

10-Sep-24

23

15-Sep-24

24

8-Oct-24

25

16-Oct-24

26

10-Nov-24

27

15-Nov-24

28

16-Nov-24

29

21-Nov-24

30

28-Nov-24

31

15-Dec-24

32

22-Dec-24

33

7-Jan-25

34

20-Jan-25

35

26-Jan-25

36

37

38

39

2g

Cell Formulas
Range	Formula
H2:I13	H2	=LET(a,A.:.A-DAY(A.:.A)+1,GROUPBY(a,a,COUNT,1,0))
Dynamic array formulas.

Dave Patton · Thursday at 10:06 PM

with custom format mmm-yy

T202503a.xlsm

H

I

1

Month

Count

2

Jan-24

3

Feb-24

1

4

Mar-24

3

5

Apr-24

3

6

Jun-24

4

7

Jul-24

2

8

Aug-24

4

9

Sep-24

2

10

Oct-24

2

11

Nov-24

5

12

Dec-24

2

13

Jan-25

3

14

2g

Cell Formulas
Range	Formula
H2:I13	H2	=LET(a,A.:.A-DAY(A.:.A)+1,GROUPBY(a,a,COUNT,1,0))
Dynamic array formulas.

alansidman · Thursday at 10:26 PM

with power query

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Received", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date Received]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date Received]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Year", type text}}, "en-US"),{"Month Name", "Year"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Month:Year"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Date Received"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Month:Year"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book5

A

B

C

D

1

Date Received

Month:Year

Count

2

1/16/2024

January:2024

3

1/17/2024

February:2024

1

4

1/18/2024

March:2024

3

5

2/25/2024

April:2024

3

6

3/10/2024

June:2024

4

7

3/21/2024

July:2024

2

8

3/29/2024

August:2024

4

9

4/3/2024

September:2024

2

10

4/6/2024

October:2024

2

11

4/23/2024

November:2024

5

12

6/5/2024

December:2024

2

13

6/10/2024

January:2025

3

14

6/13/2024

15

6/14/2024

16

7/10/2024

17

7/10/2024

18

8/8/2024

19

8/12/2024

20

8/12/2024

21

8/23/2024

22

9/10/2024

23

9/15/2024

24

10/8/2024

25

10/16/2024

26

11/10/2024

27

11/15/2024

28

11/16/2024

29

11/21/2024

30

11/28/2024

31

12/15/2024

32

12/22/2024

33

1/7/2025

34

1/20/2025

35

1/26/2025

Sheet1

COUNTIFS using MONTH and YEAR references

redhots4

Board Regular

Joe4

MrExcel MVP, Junior Admin

Joe4

MrExcel MVP, Junior Admin

redhots4

Board Regular

Joe4

MrExcel MVP, Junior Admin

Alex Blakenburg

MrExcel MVP

Joe4

MrExcel MVP, Junior Admin

Dave Patton

Well-known Member

Dave Patton

Well-known Member

alansidman

Well-known Member

Similar threads

Share this page

COUNTIFS using MONTH and YEAR references

Board Regular

MrExcel MVP, Junior Admin

MrExcel MVP, Junior Admin

Board Regular

MrExcel MVP, Junior Admin

MrExcel MVP

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock