In this tutorial, we will learn, how we can count cells with specific date range. We will be using COUNTIFS formula which uses multiple criteria to count the cells. Microsoft Excel 2021 is used.
We will be using the following data for our tutorial:
In the above image, we see a table with ranges specified from 2020 to 2022.
Note that, the format of the date will depend on your operating system settings. For this tutorial, we are using the date format as: Day/Month/Year.
We will be counting the number of employees who have joined the company within a specific date range. We will need the following parameters:
- Start Date
- End Date
- Cell Range for which we will count if a cell meets the criteria of the date.
In this Example, we are going to count how many cells are between July 2022 and December 2022. For this, we will use COUNTIFS formula.
=COUNTIFS( in any cell where you want the output.
Enter the Cell range, in this case, there is a date with every employee, we can select that as the Date range.
The Formula becomes
Now, we enter the start date from where we want to see, in our case, it is,
01/07/2022, while in American format it will be:
Note that we enter comma, after the cell range and we enter the criteria within closed quotation marks. Before entering start date, we enter the operator
>=, it will count the entered date and the dates that come after it.
So, the formula becomes:
Do not put spaces in the criteria that is written within quotation marks.
Now, we need to enter the end date, but before entering the end date, we will need to enter the cell range. We will use the same Cell range, we used earlier for start date.
So the formula becomes:
=COUNTIFS(B14:B33, ">=01/07/2022", B4:B33
And finally after putting comma, we enter the end date:
12/31/2022 for American format.
So, the final formula becomes:
=COUNTIFS(B14:B33, ">=01/07/2022", B4:B33, "<=31/12/2022")
Now, we can see that we have counted the number of cells with date range.
Similarly, we can add other dates.