Counting dates to the month

[searchingforhelp]

Hi All,

I am looking for a formula that can count the number of dates in column A that is associated with the month in column C. I was using Countif and Month formula; however, as data continues to grow, manually changing the formula by row is becoming an issue. Is there a way to have a formula for the quarter as well?

Thanking you in advance.

Calendar Days.xlsx
	A	B	C	D	E	F	G
1	Date	Month	Total by Month		Date	Quarter	Total by Quarter
2	5/10/2021	Jan-21			5/10/2021	Q1
3	7/3/2021	Feb-21			7/3/2021	Q2
4	9/21/2021	Mar-21			9/21/2021	Q3
5	12/12/2021	Apr-21			12/12/2021	Q4
6	4/15/2021	May-21			4/15/2021
7	5/20/2021	Jun-21			5/20/2021
8	6/20/2021	Jul-21			6/20/2021
9	12/1/2021	Aug-21			12/1/2021
10	8/3/2021	Sep-21			8/3/2021
11	7/15/2021	Oct-21			7/15/2021
Sheet3

 

[jasonb75]

For the months, enter this into C2 and fill down.

=COUNTIFS(A:A,">="&B2,A:A,"<="&EOMONTH(B2,0))

Excel doesn't recognise quarters so you would need to use the start date of each quarter to identify the correct date range. Then you could use the same formula as above, changing the 0 at the end to 2 so that is spans a 3 month period.

 

[searchingforhelp]

For the months, enter this into C2 and fill down.

=COUNTIFS(A:A,">="&B2,A:A,"<="&EOMONTH(B2,0))

Excel doesn't recognise quarters so you would need to use the start date of each quarter to identify the correct date range. Then you could use the same formula as above, changing the 0 at the end to 2 so that is spans a 3 month period.

Thank you Jasonb75. The first formula worked like a charm. As for the quarters, can you provide a visual example? For example what would column A look like? I set it to the first day of the Month

Calendar Days.xlsx
	A	B	C
1	Date	Quarter	Total by Quarter
2	5/1/2021	Q1	0
3	7/1/2021	Q2	0
4	9/1/2021	Q3	0
5	12/1/2021	Q4	0
6	4/1/2021
7	5/1/2021
8	6/1/2021
9	12/1/2021
10	8/1/2021
11	7/1/2021
Sheet3

Cell Formulas
Range	Formula
C2:C5	C2	=COUNTIFS(A:A,">="&B2,A:A,"<="&EOMONTH(B2,2))

 

[jasonb75]

No no no, you don't need to change the dates being counted, you need the start or each quarter as a reference point.

Using your original example (note that I've had to convert the dates to UK format so that I can test it with my settings).

Book1
	A	B	C	D	E	F	G	H
1	Date	Month	Total by Month		Date		Quarter	Total by Quarter
2	10/05/2021	Jan-21	0		10/05/2021	Jan-21	Q1	0
3	03/07/2021	Feb-21	0		03/07/2021	Apr-21	Q2	4
4	21/09/2021	Mar-21	0		21/09/2021	Jul-21	Q3	4
5	12/12/2021	Apr-21	1		12/12/2021	Oct-21	Q4	2
6	15/04/2021	May-21	2		15/04/2021
7	20/05/2021	Jun-21	1		20/05/2021
8	20/06/2021	Jul-21	2		20/06/2021
9	01/12/2021	Aug-21	1		01/12/2021
10	03/08/2021	Sep-21	1		03/08/2021
11	15/07/2021	Oct-21	0		15/07/2021
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=COUNTIFS(E:E,">="&F2,E:E,"<="&EOMONTH(F2,2))
C2:C11	C2	=COUNTIFS(A:A,">="&B2,A:A,"<="&EOMONTH(B2,0))

 

[searchingforhelp]

No no no, you don't need to change the dates being counted, you need the start or each quarter as a reference point.

Using your original example (note that I've had to convert the dates to UK format so that I can test it with my settings).

Book1
	A	B	C	D	E	F	G	H
1	Date	Month	Total by Month		Date		Quarter	Total by Quarter
2	10/05/2021	Jan-21	0		10/05/2021	Jan-21	Q1	0
3	03/07/2021	Feb-21	0		03/07/2021	Apr-21	Q2	4
4	21/09/2021	Mar-21	0		21/09/2021	Jul-21	Q3	4
5	12/12/2021	Apr-21	1		12/12/2021	Oct-21	Q4	2
6	15/04/2021	May-21	2		15/04/2021
7	20/05/2021	Jun-21	1		20/05/2021
8	20/06/2021	Jul-21	2		20/06/2021
9	01/12/2021	Aug-21	1		01/12/2021
10	03/08/2021	Sep-21	1		03/08/2021
11	15/07/2021	Oct-21	0		15/07/2021
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=COUNTIFS(E:E,">="&F2,E:E,"<="&EOMONTH(F2,2))
C2:C11	C2	=COUNTIFS(A:A,">="&B2,A:A,"<="&EOMONTH(B2,0))

Thank you!