Count Items in a Date Range in Excel

Count Items in a Date Range in Excel

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

Count Items in a Date Range

This screen shot shows a few rows of the list with the order details for this example. We’ll add a start and end date for a date range, and count items in that date range.

countifsdaterange01

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.

countifsdaterange02

Create the COUNTIFS Formula

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

=COUNTIFS(B5:B18,A2,F5:F18,”>=”&D1,F5:F18,”<=”&D2)

  • 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.

countifsdaterange03

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.

countifsdaterange04

Video: Sum in a Date Range

Using a different example, this video shows how to sum items that fall within a specified date range.

Video: Count Numbers in a Range – COUNTIFS

Using a different example, this video shows how to use the COUNTIFS function to count cells based on a range of numbers.

The minimum and maximum numbers are entered on the worksheet, so it’s easy to change the number range, when needed.

More Excel Date Tips

Fix Excel dates that won’t change Format

AutoFill Excel dates in series or same Date

How to prevent grouped dates in Excel

Monitor expiry dates in Excel

Excel filter for date range 2 Ways

________________

27 thoughts on “Count Items in a Date Range in Excel”

  1. Is it just me or is it easier to read and write
    =SUMPRODUCT((B5:B18=A2)*(F5:F18>=D1)*(F5:F18=”&D1,F5:F18,”<="&D2)
    ???

  2. 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.

  3. BILL DATE PAYMENT DATE DAY COUNT No. HOLIDAYS IN RANGE
    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
    HOLIDAY LIST
    02-07-14
    09-07-14
    24-07-14

  4. 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

  5. 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!

  6. 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

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

  8. 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

  9. 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.

  10. Hi,
    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.
    Cheers,
    Will

  11. I am looking for a formula with great difficulty
    So on Sheet 1 is the master data I have all names, managers, log in id, error code, error discription, start date.
    on sheet 2 we resord the date error occurree, log in id and error number. Vlookup then gives us the rest of the data.
    From sheet 2 I need to find out how many errors were made in a date period i.e 6th Dec to 12th Dec and to calculate the start date so how many errors were made by people who have been here 14days / 28 days / 42 days and 56+ days.
    I am finding a formula sooooo hard to come by any help is much appreciated.

  12. I am making inventory sheet and facing problem about calculation the Balance QTY. let me explain here; I have made total 6 columns;
    Col A – 10 QTY. Received
    Col B – 7 QTY. Issued
    Col C – 4 QTY. Installed
    Col D – 1 QTY. Returned
    Col E – 1 QTY. DEAD
    Col F – QTY. Balance
    Hi ALL
    I have purchased 10 items and issued 7, that means 3 items should be shown in balance, but my inventory record justifying total 6 items of column C,D and E. So it should show error in column F until I find 1 missing item? Because I have issued total 7 and if total items of C,D and E matches with column B then 3 items should show in column F Balance.
    I will really appreciate if some body help me.
    Thanks
    Asad

  13. Hi, I am looking to count the following
    I have a start date and end date
    I am looking to count the numbers in that range.
    Any help.

  14. Hi Debra,
    Thanks a lot for this formula, it proved a great help, most of my reports are automated now. Perhaps can you also help me with Argument #2
    “The 2nd argument, A2, is the range with the value for criteria 1 (Product)” here i want that it gives me count of everything except one product. example i want count of all products except PEN.
    Rest all arguments are unchanged.
    Thanks in advance.

  15. I have two range in Column A(date) & Column B(Inventory no.). Date and inventory are not unique. I need count of inventory if falls under same date in column A. Please help..

  16. Sir,
    I am using excel 2013. I am not getting the result by this formula. Want to count how many records are there in between two dates (D2 and E2). Please solve my problem and send me a excel templet.
    D2 = Start Date
    E2 = End Date
    Table2 = Name range
    COUNTIFS(Table2[IN DATE & TIME],”>=”&D2,Table2[IN DATE & TIME],”<="&E2)

  17. Hello there,

    I really appreciate the detailed information present on your Website i am stucked at one place in excel want to know if there is any Formula or VB Script which can help me.

    I am making a Leave management excel Template from my Office everything is perfect except the date.

    eg. if User X has applied leaves from 01-01-2019 till 15-01-2019

    I Blocked User Y to apply the Leaves from 1st Jan till 15 via Data Valitation not between field so now he can apply after (15-01-19 till 25-01-19)

    Now on the third row for User z i don’t know what to do if i want to block dates for User X + User Y (i.e. User Z should not choose any dates from 01-01-2019 till 15 Jan 2019 & 15-01-2019 till 25-01-2019).

    I hope i made it clear.

    Thank you in advance.

    Regards,
    Muzamil.

  18. I am trying to find a formula for a spreadsheet that covers the fiscal year with numerous entries per day. I want to have a running total for the year which is easy but then I also want a formula to number rows for the month then by the day. So far I am doing the daily manually but know there has to be a simpler way.

    In addition to this I would like to set up an auto count of my rows per day per specific data in another column. Is there a simple way to do this?

    Any assistance is greatly appreciated.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.