Skip to main content

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 and date range with formulas 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.

=SUMPRODUCT(1*(MONTH(C3:C16)=G2))

Notes:

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.

=SUMPRODUCT(1*(YEAR(C3:C16)=1988))
=SUM(IF(YEAR(B2:B15)=1988,1))

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(B2:B15,"1992-8-16")

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:

=COUNTIF(B2:B15, ">" & "1990-1-1")
=COUNTIF(B2:B15, "<" & "1990-1-1")

To count if between two specific dates (says between 1988-1-1 and 1998-1-1), please apply below 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

Easily count by Fiscal year, half year, week number, or day of week in Excel

The PivotTable Special Time Grouping feature, provided by Kutools for Excel, is able to add a helper column to calculate the fiscal year, half year, week number, or day of week based on the specified date column, and let you easily count, sum, or average columns based on the calculated results in a new Pivot Table.


Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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- Includes more than 300 handy tools for Excel. Full feature free trial 30 days, no credit card required! Get It Now

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.

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 9/14/1985, just specify the settings in the Select Specific Cells dialog box as below screenshot shown:


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


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!


Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (33)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi;

I need to determin how many times I need Know per week of the year how many time i nedd to see a patient , knowing de lenght of stay and the frequency of observation, 3 in the days ,
exemple :
patiente ex: date of admission : 8/3/2023 date of discharge : 31/8/2023 ; observations 3 in 3 days .
This comment was minimized by the moderator on the site
Hi, I need a formula to count how many times the country of El Salvador appears by Month (how many in Jan, how many if Feb, and how many in March)

1/10/2022 El Saldavor
1/11/2022 USA
1/12/2022 El Salvador
02/01/2022 El Salvador
02/06/2022 Mexico
02/05/2022 USA
03/03/2022 El Salvador
03/03/2022 El Savlador
03/03/2022 USA
This comment was minimized by the moderator on the site
Hi there,

You can add a helper column first with the formula =MONTH(data_cell) to convert the dates to corresponding month numbers. And then use a COUNTIFS formula to get the count of "El Salvador" for each month number.

For example, to get the number of El Salvador appears in January, use: =COUNTIFS(country_list,"El Salvador",helper-column,1)

Please see the picture below:
https://www.extendoffice.com/images/stories/comments/ljy-picture/count_items_by_month.png

Amanda
This comment was minimized by the moderator on the site
Προσπαθω να μετρήσω μια σιγκεκριμένη ημερομηνία αλλα τον τυπο που έχεται παραπάνω δεν το δέχεται το excel =COUNTIF(B2:B15,"1992-8-16")
This comment was minimized by the moderator on the site
Hi there,

Is it becase of the language your Excel version? Please check if COUNTIF should be converted to your language in your Excel verion. Also, when you say Excel does not accept the formula, did Excel show any errors or anything?

Amanda
This comment was minimized by the moderator on the site
01/03/2022 27/08/2022
02/02/2022 31/07/2022
01/04/2022 01/07/2022
01/04/2022 30/06/2022
01/04/2022 30/06/2022
30/04/2022 29/05/2022
20/04/2022 19/05/2022
15/04/2022 14/05/2022
15/04/2022 14/05/2022
15/04/2022 14/05/2022
04/04/2022 03/05/2022
01/02/2022 01/05/2022
13/04/2022 27/04/2022
16/04/2022 24/04/2022
04/04/2022 23/04/2022
15/04/2022 20/04/2022
21/03/2022 19/04/2022
11/04/2022 17/04/2022
05/04/2022 14/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
15/03/2022 13/04/2022
22/03/2022 10/04/2022
27/03/2022 05/04/2022
29/03/2022 04/04/2022
03/01/2022 03/04/2022
03/01/2022 03/04/2022
03/01/2022 03/04/2022
25/03/2022 03/04/2022

Como contar apenas os dias do mês 04 nesses intervalos?
This comment was minimized by the moderator on the site
Hi, are you trying to count the number of days in April?
If yes, you should first select the two columns, then go to Kutools tab, in the Ranges & Cells group, click To Actual. Now, in the Editing group, click Select, and then click Select Specific Cells.
In the pop-up dialog box, select Cell option under the Selction Type; Under Specific type, select Contains, and type 4/2022 in the corresponding box. Click OK. Now, it will tell you how many days of April are there. Please see screenshot.
This comment was minimized by the moderator on the site
i need report weekly wise like a 7th 14th 21st 28th count only.other days are no need for every month i tried many pivot but i can't find out please help me.
This comment was minimized by the moderator on the site
Why we cant justselect whole column, instead of using this MONTH(C3:C16) ?
This comment was minimized by the moderator on the site
In the formula =SUMPRODUCT(1*(MONTH(C3:C16)=G2)), G2 is the specified month number, says 4. If we replace MONTH(C3:C16) with the whole columns (or C3:C16), there is no value that equal to the value 4, therefore we cannot get the count result.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hi Roxie,
You should try the Compare cells feature, which can compare two columns of cells, find out, and highlight the exactly same cells between them or the differences. https://www.extendoffice.com/product/kutools-for-excel/excel-compare-two-cells-of-equal.html
This comment was minimized by the moderator on the site
JAN = 1
FEB = 2
..
..
DEC = 12 ? NOT CORRECT RESULT IN DECEMBER
This comment was minimized by the moderator on the site
Hi Rhon,
Could you describe more about the error? Does the error come out when converting December to 12, or when counting by “12”?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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")
This comment was minimized by the moderator on the site
rumus ini = SUMPRODUCT (1 * (YEAR (B2: B15) = 1988)) kalau datanya (range) sampe 20ribu ko ga bisa ya?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations