MoonPoint Support Logo

 


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
August
Sun Mon Tue Wed Thu Fri Sat
           
18
         
2015
Months
Aug


Tue, Aug 18, 2015 9:38 pm

Counting number of cells with date before or equal to today

If you have a range of cells in an Excel worksheet that contain a date and you wish to obtain a count of the number of those cells that have a date that is prior to or equal to today, you could use the formula below, presuming the relevant cells containing the date are in the range E2:E810:

COUNTIF(E2:E810,"<="&TODAY())

The ampersand concatenates today's date after the <=. If you wanted a count for only those dates prior to today, but not including today, you could replace the <= with <.

If you need to count cells based on multiple criteria, you can use COUNTIFS. E.g., if for the above range of cells I want to count the number of cells that not only have a date prior to or equal to today, but only count the cells if there is some value in another cell in the row, i.e., if the date in column E is prior or equal to today and there is some value in column P in that row, i.e., column P is not blank, I can use the formula below:

=COUNTIFS(E2:E810,"<="&TODAY(), P2:P810, "<>"&"")

E.g., I need to track when an expiration date stored in column E for an item has passed, but I only want to count the entries when I've contacted the user associated with the item notifying him that he needs to renew the item. In column P in the worksheet, I store the date I contacted the user. If I haven't yet contacted the user, the cell is empty.

I also want to now how many expiration notices I sent today, so use the formula below for that number:

=COUNTIFS(E2:E810,"<="&TODAY(), P2:P810, "="&TODAY())

The formula checks the values in column E from E2 to E810 to determine if the date in those cells is less than or equal to today and, if so, whether today's date is in the corresponding cell in column P. If both criteria are met, then the count is incremented by 1.

References:

  1. Countif greater than or equal to a date
    Date: February 13, 2015
    MoonPoint Support
  2. Excel countifs function
    Date: March 14, 2014
    MoonPoint Support
  3. COUNTIF in Excel - count if not blank, greater than, duplicate or unique
    By: Svetlana Cheusheva
    AbleBits

[/os/windows/office/excel] permanent link

Once You Know, You Newegg AliExpress by Alibaba.com

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo