COUNTIFS formula for number of dates in a quarter??

[mattbird]

Hi,

I am using the following formula to count the number of dates that fall between Apr and Jun:

=COUNTIFS(P13:P15,">="&"04/01/2024",P13:P15,"<="&"30/06/2024")

But I need it to be rolling, otherwise every year I'm going to have to update the formula. Is there a way around this, so regardless of the year it will count between Apr and Jun?

I am doing the same for Jul-Sep, Oct-Dec and Jan-Mar as these are my quarters for the financial year etc.

If there is a better way of doing this, please let me know.

Thanks Matt

 

[Peter_SSs]

But I need it to be rolling,

Might need a bit more detail about just what you mean by that. For a start, could you do something like this where the financial year is entered in a cell (R11 for me) and the formulas refer to that.

24 03 25.xlsm
	P	Q	R
11			2024-2025
12	Date		Results
13	1/04/2024	Apr-Jun	3
14	18/11/2024	Jul-Sep	2
15	30/06/2024	Oct-Dec	4
16	1/07/2024	Jan-Mar	1
17
18	3/10/2024
19	8/10/2024
20	8/05/2024
21	29/12/2024
22	2/02/2025
23	30/09/2024
Count Qtrs

Cell Formulas
Range	Formula
R13	R13	=COUNTIFS(P13:P23,">="&DATE(LEFT(R11,4),4,1),P13:P23,"<="&DATE(LEFT(R11,4),6,30))
R14	R14	=COUNTIFS(P13:P23,">="&DATE(LEFT(R11,4),7,1),P13:P23,"<="&DATE(LEFT(R11,4),9,30))
R15	R15	=COUNTIFS(P13:P23,">="&DATE(LEFT(R11,4),10,1),P13:P23,"<="&DATE(LEFT(R11,4),12,31))
R16	R16	=COUNTIFS(P13:P23,">="&DATE(RIGHT(R11,4),1,1),P13:P23,"<="&DATE(RIGHT(R11,4),3,31))

 

[mattbird]

Hi Peter,

Thanks for your help. your example is similar to my table, but can this be done without having R11? so no matter what the financial year is it will still count etc. I'm trying to also reduce the risk of other having to amend cells each year. Does this make sense?

 

[Peter_SSs]

your example is similar to my table, but can this be done without having R11?

Do all of the dates in column P fall within a single financial year?

When will you be using the formula? I ask because if the formula is used in, say September 2024 then the year value for the Apr-Jun quarter (for example) could be obtained by YEAR(TODAY())
However, if the formula was being used in say February 2025 then the year value for the Apr-Jun quarter would need to be YEAR(TODAY())-1

 

[Alex Blakenburg]

This might work for you.

Book3
	P	Q	R
11
12	Date		Results
13	1/04/2024	Apr-Jun	3
14	18/11/2024	Jul-Sep	2
15	30/06/2024	Oct-Dec	4
16	1/07/2024	Jan-Mar	1
17
18	3/10/2024
19	8/10/2024
20	8/05/2024
21	29/12/2024
22	2/02/2025
23	30/09/2024
Sheet1

Cell Formulas
Range	Formula
R13:R16	R13	=COUNTIFS($P$13:$P$23,">="&DATE(YEAR(MIN($P$13:$P$23)),3*ROWS(Q$13:Q13)+1,1),$P$13:$P$23,"<="&DATE(YEAR(MIN($P$13:$P$23)),3*ROWS(Q$13:Q13)+4,0))