Subtracting a range of values from a single cell value and counting only certain results in a single cell

[excelBean25]

Ok. I've usually been good about googling for solutions, but I can't figure out the following problem.

I have a range of values, in this case historical dates, and I'd like to count those values that are a certain distance from a constant value (today's date or a future date).

For example, I have the following dates in a column: 3/31/2013, 1/30/2019, 12/19/2017

In another cell, I have a date in the future from those above, say 3/25/2021.

I'd like to calculate in one cell the number of data points in the column of dates that are greater than 1000 days but less than 2000 days in the past from the future date. In this case it would be one data point.

I've googled solutions with COUNTIFS but it doesn't seem to work when subtracting a range of numbers from a single number as I'm trying to do above. This will apply to columns with hundreds of data points.

 

[samitnair]

Considering your dates are in A1 and Your reference date is in E1

Put this formula in B1 to tell you if the date value is between 1000 - 2000

=IF(AND(E1-A1<2000,E1-A1>1000),E1-A1,"")

 

[Eric W]

Welcome to the forum!

Try:

Book1 (version 1).xlsb
	A	B	C	D	E
1	Dates	Match Date	Min	Max	Matching Count
2	3/31/2013	3/25/2021	2000	1000	1
3	1/30/2019
4	12/19/2017
5
Sheet6

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS(A2:A4,">="&B2-C2,A2:A4,"<="&B2-D2)

 

[excelBean25]

I should have been clearer with images.

Let's say I have a list of dates in a tab such as:

3/31/13​

1/30/19​

12/19/17​

5/14/15​

8/30/16​

4/12/13​

4/1/13​

3/23/17​

Then in another tab in the same worksheet I have the following:

	365​	730​	1095​	1460​	1825​	2190​	2555​	2920​	3285​
	0 to 1 Years	1 to 2 Years	2 to 3 Years	3 to 4 Years	4 to 5 Years	5 to 6 Years	6 to 7 Years	7 to 8 Years	8 to 9
3/31/21​	​	​	​	​	​	​	​	​	​
4/30/21​
5/31/21​
6/30/21​
7/31/21​

The numbers in the first row above represent numbers of days.

For each row with a future date, I'd like to calculate how many data points from the first list fall within each column year range. I'd effectively be subtracting the future date from each of the historical dates and counting only those that fall within the numbers of days on the top row. (ex. 1 to 2 years would include those dates that are greater than 365 days and less than or equal to 730 days, and so on for the rest of the columns)

I'm imagining a formula in each cell that that does it all. I thought a countif formula could do it, but I can't figure it out.