How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)

Get FREE Advanced Excel Exercises with Solutions!

In MS Excel, the COUNTIF function is used to count cells under different criteria. In this article, you’ll learn effectively how you can use this COUNTIF function to count date range under multiple criteria or conditions.

countif to count date range in excel overview

The above screenshot is an overview of the article which represents the dataset & an example of the COUNTIF function to count data based on multiple criteria. You’ll get to learn more about the dataset along with all suitable functions in the following methods in this article.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Introduction to COUNTIF Function in Excel

Before getting down to the main talking point, let’s get introduced to the COUNTIF function first.

  • Objective of the Function:

Counts the number of cells within a range that meets the given condition.

  • Formula Syntax:

=COUNTIF(range, criteria)

  • Arguments:

range- Range of cells that will be subject to the criteria.

criteria- Selected criteria for the range of cells.

  • Example:

In the picture below, our dataset is present. The columns from B to F represent random names of computer brands, device categories, model names, purchase dates & delivery dates respectively.

countif introduction

With the COUNTIF function here at first, we’ll find out how many notebooks are in the table.

📌 Steps:

➤ Select Cell H15 & type:

=COUNTIF(C5:C27, "Notebook")

➤ Press Enter & you’ll get the result at once.

countif introduction

In the 1st argument, the Cell Range- C5:C27 has been added which represents all device types & then we’ve included the criteria by simply typing Notebook within Quotation Marks(“ ”). You can also use cell reference of Notebook there & you won’t have to use Quotations Marks then.


6 Suitable Uses of COUNTIF Function for Date Range in Excel

1. COUNTIF to Count Dates Excluding Blank Cells

Now we’ll deal with date range & in our 1st criteria, we’ll exclude blank cells while counting dates along with other text cells. Based on our dataset, we’ll find out the number of delivery dates & text cells excluding blank cells.

📌 Steps:

➤ The related formula in Cell H15 will be:

=COUNTIF(F5:F27,"<>"&"")

➤ Press Enter & you’ll see the result right away.

countif to count date range excluding blank cells

In this formula, we are excluding blank cells by typing “<>”&”” in the Criteria argument. By using Ampersand(&), we’re connecting the “Not Equal to” symbol with “Blank Cells”. Thus this function will exclude cells that are equal to blank cells.

Read More: Count Blank Cells with Excel COUNTIF Function: 2 Examples


2. COUNTIF to Count Dates Older than a Fixed Date

If we want to count a range of dates that are older than a fixed than then we have to use the Less Than (<) symbol before a fixed date in the Criteria argument. Assuming hare, we want to find out the number of purchase dates before 5/1/2021.

📌 Steps:

➤ In Cell H15, we have to type:

=COUNTIF(E5:E27,"<5/1/2021")

➤ Press Enter & the function will return as 12.

countif to count dates older than fixed or particular date

Read More: COUNTIF Date Is within 7 Days


3. COUNTIF to Count Dates Newer than a Fixed Date

Similarly, by using the Greater Than (>) symbol, we can find dates newer than a fixed date from our dataset. Here, we’ll find out the number of dates newer than 4/30/2021.

📌 Steps:

➤ In Cell H15, we have to type:

=COUNTIF(E5:E27,">4/30/2021")

➤ Press Enter & the resultant value will be 11.

countif to count dates newer than fixed or particular date in excel

Read More: COUNTIF Greater Than and Less Than [with Free Template]


Similar Readings


4. COUNTIF or COUNTIFS to Count Dates Between Two Fixed Dates

By subtracting one COUNTIF function from another one, we can find the number of dates between two fixed dates. If we consider the 1st date as the old date & the 2nd date as the new date to find total dates between these two, then we have to subtract newer dates than the 2nd date from the newer dates than the old date. For our dataset, thus we’ll determine the number of total purchases between the dates of 4/15/2021 & 5/15/2021.

📌 Steps:

➤ The related formula in Cell H15 will be:

=COUNTIF(E5:E27,">4/15/2021")-COUNTIF(E5:E27,">5/15/2021")

➤ Press Enter & you’ll get the result instantly.

countif to count date range between two dates in excel

Well, now by using the COUNTIFS function we can add multiple criteria for two different dates & we shan’t have to use COUNTIF functions twice to subtract anymore. So, to get the previous result by using the COUNTIFS function, we have to type in Cell H15:

=COUNTIFS(E5:E27,">4/15/2021",E5:E27,"<5/15/2021")

After pressing Enter, you’ll find the similar result that we’ve found earlier by the subtraction between two COUNTIF functions.

countifs to count date range between two dates in excel

Read More: COUNTIF vs COUNTIFS in Excel (4 Examples)


5. Combining COUNTIF with TODAY Function to Count Cells up to Current Date

In lots of cases, we need to count data up to the current date. In that case, we have to use the TODAY function with the COUNTIF function. For our dataset, we’ll find out how many purchases have been completed up to the current date (While preparing this section of the article, the current date was 7/18/2021).

📌 Steps:

➤ The related formula in Cell H15 will be:

=COUNTIF(E5:E27,"<="&TODAY())

➤ Press Enter & you’re done. You’ll get the number of total purchases up to the current date at once.

countif to count date range upto current date with today function

Read More: Excel COUNTIFS Not Working (7 Causes with Solutions)


6. COUNTIFS to Count Dates with Multiple Conditions or Criteria

In the last section, we’ll add multiple criteria or conditions to count data from a huge table or dataset. So, our criteria here comprise of Omicron brand, notebook device, OMN34 as model name, date of purchase after 4/1/2021. We’ll find out the number of total deliveries up to the current date by using the mentioned criteria.

📌 Steps:

➤ Select Cell I17 & type:

=COUNTIFS(B5:B27,I12,C5:C27,I13,D5:D27,I14,E5:E27,">4/1/2021",F5:F27,"<="&TODAY())

➤ Press Enter & you’ll get the number of total deliveries for the selected criteria right away.

countif to count date range with multiple criteria or conditions in excel

And if you want to find out if any delivery under the given criteria is still pending, then the related formula in Cell I18 will be:

=COUNTIFS(B5:B27,I12,C5:C27,I13,D5:D27,I14,E5:E27,">4/1/2021",F5:F27,"Pending")

After pressing Enter, you’ll get the number of pending deliveries at once. You can also mention cell references without using quotes around if you don’t want to type the status of the delivery.

countif to count date range with multiple criteria or conditions in excel

Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria


Concluding Words

I hope all of these methods mentioned above will now prompt you to apply in your regular Excel chores. If you have any questions or feedback, please let me know through comments. Or you can have a glance at our other interesting articles related to Excel functions on this website.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

4 Comments
  1. Thank you so much! This article was extremely helpful. The sixth section was exactly what I was looking for.

  2. I tried using the formula within a date range and it counts some of the data, but not all. I can manually see there should be 70 and it is only counting 50. I double checked all the data to make sure the dates are listed correctly. Can you help me figure out why It is not counting all the data? Do I need to clear out the dates and reinsert them?

    • Reply Avatar photo
      Osman Goni Ridwan Aug 4, 2022 at 4:20 PM

      Hello SONYA, you can face this issue because of the following reasons:
      1. Incorrect Cell Ranges used in the formula
      2. The Date format used in the formula is not correct. You have to use a similar data format both in the cells and formulas.

      Check these things in your workbook and let us know the outcome. Thanks!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo