To count cells containing a date in a specific year, use =SUMPRODUCT(--(YEAR(range)=year)) or, in Excel 2021 and Microsoft 365, =COUNTIFS(range,">="&DATE(year,1,1),range,"<="&DATE(year,12,31)). The SUMPRODUCT version is the most direct and works in all Excel versions.
Why COUNTIF Alone Can't Match a Year
Excel's COUNTIF function compares entire cell values against a criterion. A date like 15-Mar-2023 is stored as a serial number (45000 for that date), not as text "2023". If you try =COUNTIF(A2:A10,"2023"), Excel looks for cells that literally equal the text "2023"—it won't match any date. Wildcards like "*2023*" only work on text, not on real date serial numbers. So you need a function that can extract the year from each date before counting.
The SUMPRODUCT Formula (Works in Every Version)
Assume dates are in A2:A10. To count how many fall in 2023:
=SUMPRODUCT(--(YEAR(A2:A10)=2023))
How it works: YEAR(A2:A10) returns an array of years (e.g., {2022;2023;2023;2021}). The comparison =2023 yields an array of TRUE/FALSE. The double unary -- converts TRUE to 1 and FALSE to 0. SUMPRODUCT then sums that array, giving the count. For a range with dates 2022, 2023, 2023, 2021, the result is 2.
To reference a year in a cell (say B1 contains 2023), use:
=SUMPRODUCT(--(YEAR(A2:A10)=B1))
This is the simplest and most readable method. It works in Excel 2007 and later, including Excel for the web and Mac.
Using COUNTIFS with Date Boundaries (Excel 2007+)
If you prefer COUNTIFS, you can define the year as a range of dates:
=COUNTIFS(A2:A10,">="&DATE(2023,1,1),A2:A10,"<="&DATE(2023,12,31))
This counts dates from Jan 1, 2023 through Dec 31, 2023. The DATE function builds the correct serial numbers, so it's robust. If the year is in B1, use:
=COUNTIFS(A2:A10,">="&DATE(B1,1,1),A2:A10,"<="&DATE(B1,12,31))
This method is slightly longer but easier to extend if you need additional criteria (e.g., count dates in 2023 where the amount in column B is >100).
Using PivotTables for Yearly Counts
If you need to count multiple years at once, a PivotTable is faster than writing multiple formulas. Select your date column, go to Insert > PivotTable, drag the date field into the Rows area and also into the Values area (it will default to Count). Then right-click any date in the PivotTable, choose Group, select Years, and click OK. Excel groups all dates by year automatically. This is ideal for a quick overview of counts per year without any formulas.
Handling Blank Cells and Text Dates
If your range contains blank cells or text entries that look like dates (e.g., "2023-01-15" as text), the SUMPRODUCT formula will return a #VALUE! error because YEAR() cannot process text or blanks. To ignore errors, wrap the formula with IFERROR for each cell—but that's messy. A cleaner approach is to use the COUNTIFS method with date boundaries, because COUNTIFS ignores text and blanks when comparing with >= and <=. For example, =COUNTIFS(A2:A10,">="&DATE(2023,1,1),A2:A10,"<="&DATE(2023,12,31)) will only count real date serial numbers, skipping text and blanks. If you must use SUMPRODUCT, first clean your data: select the column, use Data > Text to Columns > Delimited > Finish to convert text dates to real dates, or use the DATEVALUE function in a helper column.
Counting by Year with a Helper Column
If you frequently need yearly counts, add a helper column that extracts the year: in B2 (assuming dates in A2), enter =YEAR(A2) and copy down. Then use a simple COUNTIF on that helper column: =COUNTIF(B2:B10,2023). This is the most beginner-friendly method and avoids array formulas. The downside is an extra column, but it makes your spreadsheet easier to audit and reuse.
Common Mistakes and How to Avoid Them
- Using COUNTIF with text wildcards:
=COUNTIF(A:A,"*2023*")won't work on real dates. Only use wildcards if your dates are stored as text. - Forgetting to convert text dates: If your dates are left-aligned in the cell, they're probably text. Use DATEVALUE or Text to Columns to convert them first.
- Hardcoding the year in multiple places: Put the year in a cell (e.g., B1) and reference it, so you can change one cell instead of editing every formula.
- Using SUMPRODUCT on entire columns:
=SUMPRODUCT(--(YEAR(A:A)=2023))will work but may slow down large sheets. Limit the range to your actual data (e.g., A2:A1000).
Frequently Asked Questions
Can I count dates in a year using COUNTIF with a wildcard?
No. COUNTIF wildcards only match text strings. Real dates are serial numbers, so a wildcard like "*2023*" will never match. Use SUMPRODUCT or COUNTIFS with date boundaries instead.
How do I count dates in a specific year and month?
Use SUMPRODUCT with two conditions: =SUMPRODUCT(--(YEAR(A2:A10)=2023),--(MONTH(A2:A10)=1)) counts January 2023. Or use COUNTIFS with four boundaries: start of month and end of month.
What if my dates include times? Does the formula still work?
Yes. YEAR() ignores the time portion of a date-time serial number. For example, 15-Mar-2023 14:30 still returns 2023. The COUNTIFS method with DATE(2023,1,1) and DATE(2023,12,31) also works because the boundaries are at midnight, and any time on Dec 31 is still <= Dec 31 23:59:59.
Why does my SUMPRODUCT formula return #VALUE!?
This usually happens when the range contains text that looks like a date or blank cells. YEAR() cannot process non-dates. Convert text to real dates, or use the COUNTIFS method which ignores text and blanks.
Can I use this with a dynamic array formula like FILTER?
Yes, in Microsoft 365 you can use =ROWS(FILTER(A2:A10,YEAR(A2:A10)=2023)) but that's longer. SUMPRODUCT is more concise and works in older versions too.
Related
- Snowflake String Contains: SQL Methods and Examples
- Print String and Int on Arduino
- Excel Conditional Formatting Per Row: How To
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Function to Lowercase? Use LOWERThe Excel function to convert text to lowercase is LOWER. For example, =LOWER("HELLO") returns hello, and you can…
- Simple Gradebook Spreadsheet: Setup, Formulas, and TipsA simple gradebook spreadsheet can track students, assignment scores, and current averages in Microsoft Excel. With a student…
- Excel Sentence Case Formula: One Sentence or Many?For a cell in A2, use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) to capitalize the first character and make the rest lowercase. This…
- Can You Filter Out Duplicates in Excel? Methods That WorkYes. In Excel for Microsoft 365 on Windows, you can identify duplicates with conditional formatting, filter them with…
- Sort Excel by Month: Dates, Names, and Custom Lists EasilyTo sort Excel by month, select the full data range and choose Data > Sort. Sort real date…
- Due Date Calculation in Excel: Formulas and Examples GuideFor a due date a set number of calendar days after a start date, put the start date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.