Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to Countif by date/month/year and date range in Excel?

For example, we have a membership roster with their birthdays as right screenshot shown, 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

Countif by specific month/year only in Excel

Easily sort dates by day/weekday/month/ only in Excel

Kutools for Excel's Advanced Sort utility provides us a quite easy way to sort cells by weekdays quickly as below screenshot shown. This utility also supports many flexible sorting methods, such as sort by text length, sort by last name, sort by absolute value, etc. Full Feature Free Trial 60-day!
ad sort by month

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial


arrow blue right bubbleCountif by specific month/year and date range with formulas in Excel

Supposing you are going to count birthdays which are in a specific month of 8, you can enter the formula =SUMPRODUCT(1*(MONTH(B2:B15)=8)) (B2:B15 is the birthday column, and the 8 is the specific month of August) into a blank cell, and then press the Enter key.
count the occurrences by certain month or year only

Notes:
(1) To Countif by specific year, such as 1988, you can apply the formula =SUMPRODUCT(1*(YEAR(B2:B15)=1988));
(2) The array formulas =SUM(IF(MONTH(B2:B15)=8,1)) or =SUM(IF(YEAR(B2:B15)=1988,1)) can also count by specific month/year. Please remember to press the Ctrl + Shift + Enter keys together after entering the formulas;
(3) To countif by a specific date (says 1992-8-16), please apply this formula =COUNTIF(B2:B15,"1992-8-16") ;
(4) To count if later/earlier than a specific date (says 1990-1-1), you can apply the formulas =COUNTIF(B2:B15, ">" & "1990-1-1") or =COUNTIF(B2:B15, "<" & "1990-1-1") ;
(5) To count if between two specific dates (says between 1988-1-1 and 1998-1-1), please apply this formula =COUNTIFS(B2:B15,">"&"1988-1-1",B2:B15,"<"&"1998-1-1").

note ribbon Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more…     Free trial

arrow blue right bubble Countif by specified date, year, or date range in Excel

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.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the birthday column you will count in, and click Kutools > Select > Select Specific Cells. See screenshot:
count the occurrences by certain year or date range

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 Entire row option;
(2) In the Specific type section, select the Greater than 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 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.

Notes:
(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 1/1/1990, just specify the settings in the Select Specific Cells dialog box as below screenshot shown:
count the occurrences by certain date

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


arrow blue right bubbleBatch countif by day/week/month/year with Kutools for Excel

Some users may be not familiar with formulas. This method will introduce Kutools for Excel's utilities to easily batch countif by date, week, month, year, etc. Please do as follows:

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the date cells that you will count by.

2. Click Kutools > Format > Apply Date Formatting, and then in the opening Apply Date Formatting select a date formatting in the Date formatting box based on your needs, and click the Ok button. See screenshot:
count the occurrences by day, month, weekday, year only 1
In our example, we select a date formatting which shows a date as month name in the Date formatting box.

3. Keep selecting these date cells with specified date formatting, and click Kutools > To Actual.

4. Add a blank column besides original data and give it a column header. Select original data and this blank column, and click Kutools > Content > Advanced Combine Rows. See screenshot:
doc countif by month 04doc countif by month 05

5. Configure the combining options in the opening Combine Rows Based on Column dialog box as above second screenshot shown:
(1) Select the date column that you will count by, and click the Primary Key button;
(2) Select the blank column, and click Calculate > Count;
(3) Set combining options for other columns as you need.
(4) Click the Ok button.

Till now we have counted birthday based on month/week/year/date as below screenshots shown:

Count by Year only:
count the occurrences by certain year only

Count by week only:
count the occurrences by certain weekday only

Count by date:
doc countif by month 09

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Demo: Countif by date,weekday, month, year, or date range in Excel

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


arrow blue right bubbleRelated Articles


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Roxie · 2 months ago
    What would you do if you have 1 column of events within a date range and need to count if those events had a date in another column?

    Example: I have column B as the event dates which vary each month. Column D has the date they came into a consultation. I'm trying to count how many people from that specific event for a date range came to a consultation for any date.
  • To post as a guest, your comment is unpublished.
    RHON · 3 months ago
    JAN = 1
    FEB = 2
    ..
    ..
    DEC = 12 ? NOT CORRECT RESULT IN DECEMBER
    • To post as a guest, your comment is unpublished.
      kellytte · 3 months ago
      Hi Rhon,
      Could you describe more about the error? Does the error come out when converting December to 12, or when counting by “12”?
  • To post as a guest, your comment is unpublished.
    mary · 4 months ago
    How can I count a cell on a specific day of the week. For example, I want to find a number of something on the first Sunday of the month
    • To post as a guest, your comment is unpublished.
      kellytte · 3 months ago
      Hi mary,
      In your case, you should count by the specified date. For example, count by the first Sunday of Jan, 2019 (in other words 2019/1/6), you can apply the formula =COUNTIF(E1:E16,"2019/1/6")
  • To post as a guest, your comment is unpublished.
    Sommen · 10 months ago
    rumus ini = SUMPRODUCT (1 * (YEAR (B2: B15) = 1988)) kalau datanya (range) sampe 20ribu ko ga bisa ya?
  • To post as a guest, your comment is unpublished.
    Agata · 10 months ago
    Thank you!