COUNTIF date will expire in a date range

Hi there, I have an excel spreadsheet that lists a date of expiry. What I would like to do is get a count of any dates that will expire within the two months from todays date. Is this possible?

e.g

Expiry Date
25/02/10
01/10/10
15/09/10
01/03/10

Count all dates that will expire within two months from today (02/02/10) = 2

Any help is appreciated...TY

Try:

=COUNTIF(A1:A4,">"&DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY())))

Given use of XL2007 perhaps:

=COUNTIFS(A1:A4;">="&TODAY();A1:A4;"<"&EDATE(TODAY();2))

change ranges to suit - have assumed Today is inclusive of results.

Just for clarity...

I thought you wanted the count of items that expired within the next 2 months rather than the count of items with dates occurring after 2 months from current date ?

(ie if you change your last date to say Dec from Mar the first formula suggested would return 3 whereas the second suggestion would return 1)

If that is the case and you are using XL2003, then:

Please Login or Register  to view this content.

Hi there,

Sorry to dig this old thread up, but it's helping me out with a calculation I'm trying to perform. Quick question if anyone knows: how could this formula be adjusted to reflect expiration by the end of the current year? If it helps, a cell, let's say A2 is present with today's date.

So if I have these dates:

Column J:
2/24/2011
10/31/2012
11/15/2012
12/29/2012
12/31/2012
1/15/2013

What is the formula to count the number of dates so I get a count of how many items are expiring this year (in this case, 4)?

Thanks in advance!

Edit: nevermind, I found the solution on another forum:

=SUMPRODUCT((J1:J6>=TODAY()*1)*(J1:J6<="12/31/2012"*1))

The formula will have to be edited once a year to maintain function, so if anyone knows how to make it so it can just grab the current year somehow and thus be more dynamic, that would be awesome. Thanks!

Edit #2: I was able to make it slightly more dynamic by adding a cell with the year end date and setting it to check less than or equal to that instead of a static date in the formula. This way, I can copy and sum this formula across multiple sheets which I need to do and eliminate the need to change the formula for each sheet it's counting:

=SUMPRODUCT((J1:J6>=TODAY()*1)*(J1:J6<=C6*1)) where C6 = the last day of the year (12/31/2012).