Count Items in a Date Range in Excel

If you have a list on your worksheet, and a date in each row, you can get Excel to count how many dates are in a specific date range. For example, how many Pencil shipments went out in September, based on the Ship Date column?


Use COUNTIFS and Cell References

The easiest way to find the number of shipments is to use the COUNTIFS function (Excel 2007 and later) and enter the product name, and the start and end dates on the worksheet.

In this example, there is a drop down list of products in cell A2. The Start date is entered in cell D1, and the End date in cell D2.


Create the COUNTIFS Formula

To calculate the number of Pen shipments in the date range, enter this formula in cell F2:


  • The first argument, B5:B18, is the first range to check for criteria.
  • The 2nd argument, A2, is the range with the value for criteria 1 (Product)
  • The 3rd argument, F5:F18, is the range to check for criteria 2.
  • The 4th argument, ">="&D1, is the cell with the value for criteria 2 (the Start date), and the operator to use with that value (greater than or equal to)
  • The 5th argument, F5:F18, is the range to check for criteria 3.
  • The 6th argument, "<="&D2, is the range with the value for criteria 3 (the End date), and the operator to use with that value (less than or equal to)

The result will show a count of the number of times that all the criteria are met.


To get the total units for a different date range, change the product name or start and end dates in the heading section. In the next screen shot, the result shows that there was 1 Binder shipment between August 15th and September 15th.


Dashboard Course Discount Deadline

And speaking of dates, today is the deadline to get a 20% discount on Mynda Treacy’s upcoming Excel Dashboard Course. It’s a great course, and will quickly get you up and running with dashboards.

Excel Dashboard Course


15 comments to Count Items in a Date Range in Excel

  • Jason M

    Is it just me or is it easier to read and write



  • Jason M

    Last comment cobbled together everything. =SUMPRODUCT((B5:B18=A2)*(F5:F18>=D1)*(F5:F18<=D2))

  • Kobain

    Why not have an Excel file for download?

  • Manny

    Is it possible to create a cell that includes D1 & D2 in the same cell? I want a cell to say January by using quotation marks but the cell itself should be the date range between the first of January and the last day of January.


    01-07-14 05-07-14 4.00 Here count of holidays in between bill date and payment date.
    08-07-14 15-07-14 7.00
    01-07-14 15-07-14 14.00
    08-07-14 13-07-14 5.00
    20-07-14 25-07-14 5.00


  • Please rely me
    i wanna count how many dates are having in three column if there is any date then count 1 or no any date in three column then count 0

    Center PM Plan Date Oct-14 2nd PM Plan Date 3rd PM Plan Date
    Bannu-1 8-Oct-14 10-Oct-14 20-Oct-14

  • Jess

    How can I count how many unique dates there are within the range specified by two input cells?

    Ex: Teams are expected to complete a certain task every day. They can, however, complete the task multiple times in one day.

    The formula Debra shared(=COUNTIFS(B5:B18,A2,F5:F18,">="&D1,F5:F18,"<="&D2)) works excellently to calculate how many times the team has completed the task within the specified time period. But how can I count how many unique days during that time period the team completed the task (or conversely, how many days did they skip the task?)?

    I want something that looks like this:

    Start date: 4/27/2015
    End date: 5/1/2015

    # of tasks completed: 9
    # of days in compliance: 4
    # of days out of compliance: 1

    4/27/2015 - 5 tasks
    4/28/2015 - 2 tasks
    4/29/2015 - 1 task
    4/30/2015 - 0 tasks
    5/1/2015 - 1 task

    Any advice is welcomed. Thank you!

  • Jess

    Oops! I'm looking for a way to count UNIQUE days, so my data table probably should've looked like this, for a better example:

    # of tasks completed: 7
    # of days in compliance: 4
    # of days out of compliance: 1

    4/27/2015 - 1 task
    4/27/2105 - 1 task
    4/27/2015 - 1 task
    4/28/2015 - 0 task
    4/29/2015 - 1 task
    4/29/2015 - 1 task
    4/30/2015 - 1 task
    5/1/2015 - 1 task

  • frank

    I tried the countifs formula but couldn't get it to work to set up a simple table that tells me how often some material was counted within the month. Any help is greatly appreciated!!

    =COUNTIFS('MI24'!A:D,'YTD MCBA'!$A4,'MI24'!D:D,">="&'YTD MCBA'!F1,'MI24'!D:D,"<="&'YTD MCBA'!F2)
    The data is sorted as follows:

    Material Pstng Date
    10000005 1/7/2015
    10000005 1/15/2015
    10000005 1/21/2015
    10000005 1/22/2015
    10000005 1/28/2015
    10000005 2/4/2015
    10000005 2/5/2015
    10000005 2/11/2015
    10000005 2/18/2015
    10000005 2/19/2015
    10000005 2/20/2015
    10000005 2/26/2015
    10000005 3/5/2015
    10000005 3/6/2015
    10000005 3/13/2015
    10000005 3/19/2015
    10000005 3/27/2015

  • frank

    The table I am setting up... I get a #value error right now.

    1/1/2015 2/1/2015
    1/31/2015 2/28/2015
    Material # of counts Jan Feb
    10000005 32 #VALUE!
    10000007 33

  • ashok kumar yadav

    I want to count date to date in day like 12/05/2015 to 17/05/2015=6
    but I can'nt count

  • helmy

    thanks a lot for help
    i was trying to count how many dates are laying between today and the coming 7 days
    i was using the function countifs(range,">=today()",range,"today()+7")
    it was not working until i notice you used in your example ">="&D1 so i changed my formula to countifs(range,">="&today(),range,"<="&today()+7)and it worked.

  • Excelnovice


    This is great for what im trying to do, how would I do this if I had the same table on different sheets. Using the example if I had more than one store where I was shipping pens and whatever from and had the information on different sheets. I want to be able to surmise this information in a separate summary sheet to keep things clear. I think it would involve putting something in the formula like Store1 before the cells but I don't know.



Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>