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

excelBean25

New Member
Joined
Mar 25, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

samitnair

Board Regular
Joined
Jul 5, 2010
Messages
155
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
Joined
Aug 18, 2015
Messages
10,822
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
Joined
Mar 25, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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 Years1 to 2 Years2 to 3 Years3 to 4 Years4 to 5 Years5 to 6 Years6 to 7 Years7 to 8 Years8 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top