Excel COUNTIF Year in Date: Formula & Examples

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.