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

[alphazero]

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!

 

[eliW]

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

 

[Mark W.]

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

 

[alphazero]

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:

 

[Dave Patton]

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.

 

[alphazero]

That worked fantastic! Thanks a lot.

New to this, but learning.

Thanks again.

 

[alphazero]

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?

 

[Aladin Akyurek]

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

 

[Dave Patton]

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.

 

[alphazero]

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.