# Counting number of cells in a range with dates over a certai

#### alphazero

##### New Member
I have a list of dates. I want to be able to count how many of those fields have dates that are 90 days or less from today's date.

Sorry if this has been covered before... I did try a search, but didn't find anthing.

Thanks!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
On 2002-08-26 07:28, alphazero wrote:
I have a list of dates. I want to be able to count how many of those fields have dates that are 90 days or less from today's date.

Sorry if this has been covered before... I did try a search, but didn't find anthing.

Thanks!
Hi alphazero, and welcome to the board.

Assuming your data is from A2:A100

=SUMPRODUCT((A2:A100<=TODAY())*(A2:A100>=(TODAY()-90)))

Eli
This message was edited by eliW on 2002-08-26 07:41

Can you be more specific? It's not clear whether you're looking for dates in the past or the future? Or both?

Thanks for the quick replies. I tried the above formala, but am only getting a "volatile" message about my cell's value.

To be more specific, I have a list of dates, all of which are in the past. Of that list of dates, I want to be able to open the excel file at any time (any day) and have a cell that tells me how many cells have dates in them that are 90 days or less from today's date (today's date = date the file is open - so value will change from day to day).

I hope that is clear. Thanks for the help so far. :wink:

On 2002-08-26 07:52, alphazero wrote:
Thanks for the quick replies. I tried the above formala, but am only getting a "volatile" message about my cell's value.

To be more specific, I have a list of dates, all of which are in the past. Of that list of dates, I want to be able to open the excel file at any time (any day) and have a cell that tells me how many cells have dates in them that are 90 days or less from today's date (today's date = date the file is open - so value will change from day to day).

I hope that is clear. Thanks for the help so far. /board/images/smiles/icon_wink.gif

try

=COUNTIF(rDateInfo,">"&TODAY()-90)

I named the date range but that is not essential; you can replace rDateInfo with your range of dates.

That worked fantastic! Thanks a lot.

New to this, but learning.

Thanks again.

Ok. One more challeng. :wink:
What if I wanted to filter these with another variable that exists on each line?

I.e. - I have list of dates with people's names attached to them... and I only want count the ones that are associated with "Joe Schmoe". Is this possible?

On 2002-08-26 08:55, alphazero wrote:
Ok. One more challeng. :wink:
What if I wanted to filter these with another variable that exists on each line?

I.e. - I have list of dates with people's names attached to them... and I only want count the ones that are associated with "Joe Schmoe". Is this possible?

=SUMPRODUCT((DateRange<=TODAY()-90)*(NamesRange="Joe Schmoe")

will give you a count of dates 90 days or older which are associated with "Joe Schmoe".

Change<= to >= for "90 days or less".
This message was edited by Aladin Akyurek on 2002-08-26 10:58

On 2002-08-26 08:55, alphazero wrote:
Ok. One more challeng. :wink:
What if I wanted to filter these with another variable that exists on each line?

I.e. - I have list of dates with people's names attached to them... and I only want count the ones that are associated with "Joe Schmoe". Is this possible?

try

=SUMPRODUCT((A2:A4=F1)*(B2:B4>TODAY()-90))

In the above, the criteria Joe Schmoe is in F1.

Revise the ranges to fit your information.

Ok. THanks a lot.

I have another formula I am trying to get working:

=COUNTIF(AVERAGE(F23:F31), B5)

Basically, I want to get the average of a group of numbers... but only include the numbers associated with the value of "B5". I keep getting a message that I have an error... but can't see what is wrong with the above formula.

Replies
4
Views
56
Replies
3
Views
286
Replies
5
Views
287
Replies
11
Views
705
Replies
3
Views
477

1,219,578
Messages
6,149,097
Members
450,859
Latest member
njaitley

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