How to Countif by date/month/year and date range in Excel?
For example, we have a membership roster with their birthdays, and now we are preparing to make birthday cards for these members. Before making the birthday cards, we have to count how many birthdays are in a specific month/year/date. Here I will guide you to Countif by date/month/year and date range with formulas in Excel with following methods:
- Countif by specific month/year with formulas in Excel
- Countif by specified year or date range in Excel
In this section, I will introduce some formulas to count birthdays by a certain month, year, or date range in Excel.
Countif by a certain month
Supposing you are going to count birthdays which are in a specific month of 8, you can enter below formula into a blank cell, and then press the Enter key.
In above formula, C3:C16 is the specified Date of Birth column you will count birthdays in, and G2 is the cell with the specific month number.
You can also apply this array formulas =SUM(IF(MONTH(B2:B15)=8,1)) (press Ctrl + Shift + Enter keys) to count birthdays by the specific month.
Countif by a certain year
If you need to count the birthdays by a certain year, says 1988, you can use one of below formulas as you need.
Note: The second formula an array formula. Please remember to press the Ctrl + Shift + Enter keys together after entering the formulas;
Countif by a certain date
If you need to count by a specific date (says 1992-8-16), please apply below formula, and press the Enter key.
Countif by a certain date range
If you need to count if later/earlier than a specific date (says 1990-1-1), you can apply the below formulas:
To count if between two specific dates (says between 1988-1-1 and 1998-1-1), please apply below formula:
If you have Kutools for Excel installed, you can apply its Select Specific Cells utility to count the number of occurrences by specified date, year, or date range in Excel easily.
1. Select the birthday column you will count in, and click Kutools > Select > Select Specific Cells. See screenshot:
2. In the opening Select Specific Cells dialog box, please do as above screenshot shown:
(1) In the Selection type section, please check one option as you need. In our case, we check the Cell option;
(2) In the Specific type section, select the Greater than or equal to from the first drop down list, and then type the first date of the specified date range in right box; next select the Less than or equal to from the second drop down list, and type the last date of the specified date range in right box, and then check And option;
(3) Click the Ok button.
3. Now a dialog box pops out and shows how many rows have been selected as below screenshot shown. Please click the OK button to close this dialog box.
(1) For counting the number of occurrences by specified year, just specified the date range from the first date of this year to the last date of this year, such as from 1/1/1990 to 12/31/1990.
(2) For counting the number of occurrences by specified date, such as 9/14/1985, just specify the settings in the Select Specific Cells dialog box as below screenshot shown:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!