COUNTIFS using MONTH and YEAR references

[redhots4]

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
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
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

 

[redhots4]

Thank you for the replies, everyone. I'm surprised there isn't a way to simply look at the year & month in Col C, then go to the days in Col A and count the number dates that have the same year and month.

 

[Joe4]

Thank you for the replies, everyone. I'm surprised there isn't a way to simply look at the year & month in Col C, then go to the days in Col A and count the number dates that have the same year and month.

You could, but Alex's formula is actually simpler than that. What you are describing actually involves having to use two criteria (check month and year) where Alex's just checks the date.
And I believe since you would have to do the computation to pull out month and year BOTH to your criteria AND the all data you are checking, I think you would not be able use COUNTIFS, but would have to use a more complex SUMPRODUCT (which is not as intuitive to understand).

It is helpful to really understand how Excel stores dates. It actually stores dates as numbers, specifically the number of days since 1/0/1900.
If you enter any valid date in Excel, and change the format to "General", you will see the date as Excel really sees the date.
Working with this Integer allows you to do lots of mathematic computations on the dates more easily (as opposed having to parse out the month and year separately and check each one).

 

[fahad_ibnfurjan]

srt.xlsm
	A	B	C	D
1	Date Received		Month	Count
2	01/16/24		Jan-24	3
3	01/17/24		Feb-24	1
4	01/18/24		Mar-24	3
5	02/25/24		Apr-24	3
6	03/10/24		Jun-24	4
7	03/21/24		Jul-24	2
8	03/29/24		Aug-24	4
9	04/03/24		Sep-24	2
10	04/06/24		Oct-24	2
11	04/23/24		Nov-24	5
12	06/05/24		Dec-24	2
13	06/10/24		Jan-25	8
14	06/13/24		Feb-25	3
15	06/14/24
16	07/10/24
17	07/10/24
18	08/08/24
19	08/12/24
20	08/12/24
21	08/23/24
22	09/10/24
23	09/15/24
24	10/08/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	01/07/25
34	01/20/25
35	01/26/25
36	01/27/25
37	01/28/25
38	01/29/25
39	01/30/25
40	01/31/25
41	02/01/25
42	02/02/25
43	02/03/25
Sheet5

Cell Formulas
Range	Formula
C2:C43	C2	=IFERROR(TEXT(AGGREGATE(15,6,$A$2:$A$200/(ROW($A$2:$A$200)-ROW($A$2)+1=MATCH(TEXT($A$2:$A$200,"[$-en-US]mmm-yy;@"),TEXT($A$2:$A$200,"[$-en-US]mmm-yy;@"),0)),ROW(A2:A200)),"[$-en-US]mmm-yy;@"),"")
D2:D43	D2	=SUM(--($C2=TEXT($A$2:$A$200,"[$-en-US]mmm-yy;@")))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dave Patton]

With Excel 365, build your own function see example or use sum or sumproduct.

T202503a.xlsm
	A	B	C	D	E	F	G
1	Date mmm-yy	Count	Date mmm-yy	Count	Custom format mmm-yy	Count	Count
2	Jan-24	3	Jan-24	3	Jan-24	3	3
3	Feb-24	1	Feb-24	1	Feb-24	1	1
4	Mar-24	3	Mar-24	3	Mar-24	3	3
5	Apr-24	3	Apr-24	3	Apr-24	3	3
6	Jun-24	4	Jun-24	4	May-24	0	0
7	Jul-24	2	Jul-24	2	Jun-24	4	4
8	Aug-24	4	Aug-24	4	Jul-24	2	2
9	Sep-24	2	Sep-24	2	Aug-24	4	4
10	Oct-24	2	Oct-24	2	Sep-24	2	2
11	Nov-24	5	Nov-24	5	Oct-24	2	2
12	Dec-24	2	Dec-24	2	Nov-24	5	5
13	Jan-25	3	Jan-25	3	Dec-24	2	2
14					Jan-25	3	3
15		34		34		34	34
16
2g

Cell Formulas
Range	Formula
A1:B13	A1	=NumDatesByMonth(DataDates!A.:.A)
C1:D13	C1	=NumDatesByMonth(DataDates!A1.:.A1000)
F2:F14	F2	=SUM(--(TEXT(DataDates!A2:A35,"mmm-yy")=TEXT(E2,"mmm-yy")))
G2:G14	G2	=SUMPRODUCT(--(TEXT(DataDates!A2:A35,"mmm-yy")=TEXT(E2,"mmm-yy")))
F15:G15,D15,B15	F15	=SUM(F2:F14)
Dynamic array formulas.

Lambda Functions
Name	Formula
NumDatesByMonth	=LAMBDA(rngDates,LET(x,rngDates,a,LET(d,x-DAY(x)+1,GROUPBY(d,d,COUNT,1,0)),VSTACK({"Date mmm-yy","Count"},HSTACK(TEXT(INDEX(a,,1),"mmm-yy"),INDEX(a,,2)))))