Auto Counting by date

[abdulla88]

Hello everybody,

Can someone help me solve this equation?
I would like to calculate the number of nationalities by date priority (5 days before starting today), as shown in the small table.

New Microsoft Excel Worksheet (2).xlsx
	A	B	C	D	E
1	Date	Nationality
2	25-Jul-21	citizen
3	24-Jul-21	Foreigner
4	22-Jul-21	citizen
5	22-Jul-21	citizen
6	21-Jul-21	citizen
7	20-Jul-21	Foreigner
8	01-Jul-21	citizen
9	02-Jul-21	Foreigner
10	10-Jul-21	citizen
11	26-Jul-21	citizen
12
13				Total	Nationality
14				1	Foreigner
15				4	citizen
Sheet1

 

[Snakehips]

@abdulla88 Try this.

MRXLMAY21.xlsm
	A	B	C	D	E
1	Date	Nationality
2	25-Jul-21	citizen
3	24-Jul-21	Foreigner
4	22-Jul-21	citizen
5	22-Jul-21	citizen
6	21-Jul-21	citizen
7	20-Jul-21	Foreigner
8	01-Jul-21	citizen
9	02-Jul-21	Foreigner
10	10-Jul-21	citizen
11	26-Jul-21	citizen
12
13				Total	Nationality
14				1	Foreigner
15				4	citizen
Result

Cell Formulas
Range	Formula
D14:D15	D14	=COUNTIFS(B:B,E14,A:A,">"&TODAY()-5,A:A,"<="&TODAY())

Hope that helps.

 

[abdulla88]

@abdulla88 Try this.

MRXLMAY21.xlsm
	A	B	C	D	E
1	Date	Nationality
2	25-Jul-21	citizen
3	24-Jul-21	Foreigner
4	22-Jul-21	citizen
5	22-Jul-21	citizen
6	21-Jul-21	citizen
7	20-Jul-21	Foreigner
8	01-Jul-21	citizen
9	02-Jul-21	Foreigner
10	10-Jul-21	citizen
11	26-Jul-21	citizen
12
13				Total	Nationality
14				1	Foreigner
15				4	citizen
Result

Cell Formulas
Range	Formula
D14:D15	D14	=COUNTIFS(B:B,E14,A:A,">"&TODAY()-5,A:A,"<="&TODAY())

Hope that helps.

Many thanks,


it's working like magic