Here’s an overview of the sample dataset and an example of the COUNTIF function to count data based on multiple criteria. The function is counting the deliveries based on five criteria from the dataset to the right.
Download the Practice Workbook
Introduction to the COUNTIF Function in Excel
- 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 sample dataset, the columns from B to F represent random names of computer brands, device categories, model names, purchase dates, and delivery dates. We’ll find how many notebooks are in the table.
Steps:
- Select Cell H15 and insert:
=COUNTIF(C5:C27, "Notebook")- Press Enter and you’ll get the result.
In the 1st argument, the Cell Range C5:C27 has been added which represents all device types. We’ve included the criteria by simply typing Notebook within Quotation Marks(“ ”). You can also use a cell reference of a cell that contains Notebook (outside of the main dataset), which allows you to search more dynamically.
6 Suitable Uses of the COUNTIF Function for a Date Range in Excel
Example 1 – COUNTIF to Count Dates Excluding Blank Cells
Steps:
- Insert the following formula in cell H15, then hit Enter.
=COUNTIF(F5:F27,"<>"&"")
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”.
Read More: Count Blank Cells with Excel COUNTIF Function: 2 Examples
Example 2 – COUNTIF to Count Dates Before a Fixed Date
We want to find out the number of purchase dates before 5/1/2021.
Steps:
- Insert the following formula in cell H15, then hit Enter.
=COUNTIF(E5:E27,"<5/1/2021")
Read More: COUNTIF Date Is within 7 Days
Example 3 – COUNTIF to Count Dates After a Fixed Date
We’ll determine the number of dates newer than 4/30/2021.
Steps:
- Insert the following formula in cell H15, then hit Enter.
=COUNTIF(E5:E27,">4/30/2021")
Read More: COUNTIF Greater Than and Less Than [with Free Template]
Similar Readings
- COUNTIF Excel Example (22 Examples)
- How to Use COUNTIF with WEEKDAY in Excel
- COUNTIF Multiple Ranges Same Criteria in Excel
- How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)
- Compare Two Tables and Highlight Differences in Excel (4 Methods)
Example 4 – COUNTIF or COUNTIFS to Count Dates Between Two Fixed Dates
We’ll determine the number of total purchases between 4/15/2021 and 5/15/2021.
Steps:
- Insert the following formula in cell H15, then hit Enter.
=COUNTIF(E5:E27,">4/15/2021")-COUNTIF(E5:E27,">5/15/2021")
The COUNTIFS function allows us to add multiple criteria for two different dates.
- Insert the following formula in cell H15, then hit Enter.
=COUNTIFS(E5:E27,">4/15/2021",E5:E27,"<5/15/2021")
Read More: COUNTIF vs COUNTIFS in Excel (4 Examples)
Example 5 – Combining COUNTIF with the TODAY Function to Count Cells up to the Current Date
We’ll find how many purchases have been completed up to the current date.
Steps:
- Insert the following formula in cell H15, then hit Enter.
=COUNTIF(E5:E27,"<="&TODAY())
Read More: Excel COUNTIFS Not Working (7 Causes with Solutions)
Example 6 – COUNTIFS to Count Dates with Multiple Conditions or Criteria
We’ll return the items that fulfill the following criteria: Omicron brand, notebook device, OMN34 as model name, and date of purchase after 4/1/2021. We’ll also find the number of total deliveries up to the current date for those conditions.
Steps:
- Insert the following formula in cell I17, then hit Enter.
=COUNTIFS(B5:B27,I12,C5:C27,I13,D5:D27,I14,E5:E27,">4/1/2021",F5:F27,"<="&TODAY())
- To find any delivery under the given criteria that is still pending, insert the following formula in Cell I18:
=COUNTIFS(B5:B27,I12,C5:C27,I13,D5:D27,I14,E5:E27,">4/1/2021",F5:F27,"Pending")
Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
Related Articles
- How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)
- VBA COUNTIF Function in Excel (6 Examples)
- How to Use COUNTIF Between Two Numbers (4 Methods)
- COUNTIF between Two Cell Values in Excel (5 Examples)
- How to Use COUNTIF to Count Cells Greater Than 0 in Excel
Thank you so much! This article was extremely helpful. The sixth section was exactly what I was looking for.
Glad to know that the article added some value to your work, Sadie!
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?
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!
I have a spreadsheet that has a column for name, active date and inactive date. I need to somehow do a count of users that were active during a certain month. For example, I need to know how many users were active in say, June 2024. According to the data below, the first 4 should be counted (test 2 and 3 is still active). Test 5 wouldn’t be counted because they left in May. I have no idea how to write a formula for that. Can anyone help?
A) B) C)
Name Active date Inactive Date
Test 11/15/23 06/15/24
Test2 09/28/23
Test3 05/11/24
Test4 12/06/23 08/15/24
Test5 01/15/24 05/03/24
Hello Candy Neal,
To count how many users were active during June 2024, you can use a formula that checks if the active date is before or equal to the end of the month and if the inactive date (if it exists) is after the beginning of the month.
Insert the following formula in cell D2 and drag it down:
=IF(AND(B2<=DATE(2024,6,30), OR(C2="", C2>=DATE(2024,6,1))), 1, 0)
If you want to use it for other months change the date range in the formula.
To count total active users, insert the following formula in E2 cell:
=SUM(D2:D6)
Download the Excel file:
Count Total Active Users.xlsx
For example 3, 4 and 6, can we change the date (“>4/15/2021”) to date that we have entered in another cell? Because I don’t want to change the formula anytime I need different date range
Hello Edward M,
You can definitely modify the formula to reference a date in another cell instead of hardcoding it. For example, if you have a date in cell A1, you can use the formula =COUNTIF(E5:E27,”>” & A1) to make it dynamic. This way, you won’t have to change the formula every time you need a different date range.
Regards
ExcelDemy
Very helping. Thank you for your kind answer.
Hello Edward M,
You are most welcome. Thanks for your appreciation. Glad to hear that it is helpful to you. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy