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

#### excelBean25

##### New Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### samitnair

##### Board Regular
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

Excel Formula:
``=IF(AND(E1-A1<2000,E1-A1>1000),E1-A1,"")``

#### Eric W

##### MrExcel MVP
Welcome to the forum!

Try:

Book1 (version 1).xlsb
ABCDE
1DatesMatch DateMinMaxMatching Count
23/31/20133/25/2021200010001
31/30/2019
412/19/2017
5
Sheet6
Cell Formulas
RangeFormula
E2E2=COUNTIFS(A2:A4,">="&B2-C2,A2:A4,"<="&B2-D2)

#### excelBean25

##### New Member
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.

Replies
8
Views
343
Replies
4
Views
119
Replies
5
Views
100
Replies
1
Views
200
Replies
2
Views
129

1,186,112
Messages
5,955,907
Members
438,225
Latest member
rsur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back