Rank based on 2 Dates(May 1st to 30 April)

[aravindhan_31]

Hi,
Hope you are all having a wonderful day. I need some help to rank 1,2,3 etc based on criteria.
I have the date of leaving in column A & I want to rank them based on the oldest to the newest, however
1 . I need to rank unique number 1,2,3 if the date of leaving is same ( I was able to get that via this formula =RANK(A2,$A$2:$A$24,1)+COUNTIF($A$2:A2,A2)-1),
but I want to use this condition only for a particular date range . The academic year starts always from 1st May to April 30 of a year, I want to rank all date of leaving within that period. example
If have date of 10 date of leaving within 1 May 2019 till 30 Apr 20, then 1 to 10 to be ranked, and if any other date after April-2020 that is 01-May-2020, then again the ranking should start from 1 again.
The start date and end date is not there in the file, hence to be added in formula ( always 1May to 30 April) and date of leaving column is not sorted

I am not able to add using Xl2BB, hence posting image.

Thanks for the help in advance

 

[bebo021999]

Book2
	A	B	C	D	E
1
2	26/12/2015	01/05/2015	30/04/2016		1
3	26/12/2015	01/05/2015	30/04/2016		2
4	26/12/2015	01/05/2015	30/04/2016		3
5	26/12/2015	01/05/2015	30/04/2016		4
6	02/11/2018	01/05/2018	30/04/2018		1
7	03/05/2019	01/05/2019	30/04/2019		1
8	06/06/2019	01/05/2019	30/04/2019		2
9	08/06/2019	01/05/2019	30/04/2019		3
10	13/06/2019	01/05/2019	30/04/2019		4
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=SUMPRODUCT(($A$2:$A$10+ROW($A$2:$A$10)*10^-2<=A2+ROW()*10^-2)*($B$2:$B$10=B2)*($C$2:$C$10=C2))

 

[aravindhan_31]

Book2
	A	B	C	D	E
1
2	26/12/2015	01/05/2015	30/04/2016		1
3	26/12/2015	01/05/2015	30/04/2016		2
4	26/12/2015	01/05/2015	30/04/2016		3
5	26/12/2015	01/05/2015	30/04/2016		4
6	02/11/2018	01/05/2018	30/04/2018		1
7	03/05/2019	01/05/2019	30/04/2019		1
8	06/06/2019	01/05/2019	30/04/2019		2
9	08/06/2019	01/05/2019	30/04/2019		3
10	13/06/2019	01/05/2019	30/04/2019		4
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=SUMPRODUCT(($A$2:$A$10+ROW($A$2:$A$10)*10^-2<=A2+ROW()*10^-2)*($B$2:$B$10=B2)*($C$2:$C$10=C2))

Hi Bebo,

Thanks a lot this one worked and super excited now.
quick check:
In this we are using helper columns to identify start date and end date for each of those dates. is there a way we can do this within the formula, so that I don't have to add those 2 columns

Thanks again

 

[Phuoc]

Try

Book1
	A	B
1
2	12/26/2015	1
3	12/26/2015	2
4	12/26/2015	3
5	12/26/2015	4
6	11/02/2018	1
7	05/03/2019	1
8	06/06/2019	2
9	04/01/2020	3
10	04/08/2020	4
11	04/15/2020	5
12	04/22/2020	6
13	04/29/2020	7
14	06/15/2020	1
15	09/13/2020	2
16	12/12/2020	3
17	03/12/2021	4
18	06/10/2021	1
19	09/08/2021	2
20	12/07/2021	3
21	03/07/2022	4
22	06/05/2022	1
23	09/03/2022	2
24	12/02/2022	3
25	12/02/2022	4
Sheet1

Cell Formulas
Range	Formula
B2:B25	B2	=COUNTIFS($A$2:$A$24,"<"&A2,$A$2:$A$24,">="&DATE(YEAR(A2)-(MONTH(A2)<5),5,1))+COUNTIF($A$2:A2,A2)

 

[aravindhan_31]

Try

Book1
	A	B
1
2	12/26/2015	1
3	12/26/2015	2
4	12/26/2015	3
5	12/26/2015	4
6	11/02/2018	1
7	05/03/2019	1
8	06/06/2019	2
9	04/01/2020	3
10	04/08/2020	4
11	04/15/2020	5
12	04/22/2020	6
13	04/29/2020	7
14	06/15/2020	1
15	09/13/2020	2
16	12/12/2020	3
17	03/12/2021	4
18	06/10/2021	1
19	09/08/2021	2
20	12/07/2021	3
21	03/07/2022	4
22	06/05/2022	1
23	09/03/2022	2
24	12/02/2022	3
25	12/02/2022	4
Sheet1

Cell Formulas
Range	Formula
B2:B25	B2	=COUNTIFS($A$2:$A$24,"<"&A2,$A$2:$A$24,">="&DATE(YEAR(A2)-(MONTH(A2)<5),5,1))+COUNTIF($A$2:A2,A2)

This one as well worked.. thank you so much.............