Excel COUNTIF Within a Year: Date Range Formula

Formulas & functions · Updated November 22, 2026

To count cells with dates within a specific year in Excel, use COUNTIFS with two date criteria: =COUNTIFS(A2:A100,">="&DATE(2024,1,1),A2:A100,"<="&DATE(2024,12,31)). This counts every date in A2:A100 that falls between January 1 and December 31 of 2024. If your dates include times, use "<"&DATE(2025,1,1) as the upper bound instead to avoid missing entries on Dec 31 at any time.

Why COUNTIF Alone Won't Work for a Year

The single-criteria COUNTIF function can only test one condition, like =COUNTIF(A:A,">=1/1/2024"). That would count everything from Jan 1 onward, including dates in 2025 and later. To isolate a single year, you need a lower bound and an upper bound, which requires either COUNTIFS (for multiple criteria) or a SUMPRODUCT formula that checks the year directly.

Excel stores dates as serial numbers, so comparing them with >= and <= works reliably. The key is to build the date boundaries correctly so Excel interprets them as dates, not text.

Using COUNTIFS with DATE() for a Specific Year

The cleanest approach uses COUNTIFS with two criteria ranges and the DATE function to avoid locale issues with date strings.

Assume your dates are in A2:A100. To count dates in 2024:

=COUNTIFS(A2:A100,">="&DATE(2024,1,1),A2:A100,"<="&DATE(2024,12,31))

This returns the number of cells where the date is on or after Jan 1, 2024, and on or before Dec 31, 2024. The ampersand (&) concatenates the comparison operator with the date serial number. If your data includes timestamps (e.g., 12/31/2024 3:00 PM), the <= with Dec 31 at midnight will miss those. Safer upper bound:

=COUNTIFS(A2:A100,">="&DATE(2024,1,1),A2:A100,"<"&DATE(2025,1,1))

This counts everything from Jan 1, 2024, up to but not including Jan 1, 2025, covering all times on Dec 31, 2024.

Using SUMPRODUCT to Count by Year

If you prefer a formula that extracts the year directly, SUMPRODUCT works well and is more flexible for criteria like “year equals 2024” without hardcoding dates.

With dates in A2:A100:

=SUMPRODUCT(--(YEAR(A2:A100)=2024))

The YEAR function returns the year for each date, and the double negative (--) converts TRUE/FALSE to 1/0 so SUMPRODUCT can sum them. This formula is easier to read if you change the year often, but note it will ignore blank cells and text. If any cell contains text, YEAR returns #VALUE!, so this works best when the range is purely dates.

Counting in a Rolling 12-Month Period

Sometimes “within a year” means the last 365 days from today, not a calendar year. Use COUNTIFS with TODAY():

=COUNTIFS(A2:A100,">="&TODAY()-365,A2:A100,"<="&TODAY())

This counts dates from 365 days ago through today. Note that this includes today's date and excludes tomorrow. If you want exactly the last 12 months including today, this works. If you want to exclude today, change the upper bound to TODAY()-1. Because TODAY() is volatile, the formula recalculates whenever the workbook opens or changes.

Common Problems and How to Fix Them

  • Dates stored as text: If your cells contain “2024-01-15” as text, COUNTIFS won't compare them correctly. Convert text to dates using DATEVALUE in a helper column, or use --A2:A100 in SUMPRODUCT if the text is a recognizable date format.
  • Blank cells or errors: COUNTIFS ignores blanks, but SUMPRODUCT with YEAR will return #VALUE! if any cell has an error. Wrap with IFERROR or clean the data first.
  • Locale issues with date strings: Writing ">=1/1/2024" may be interpreted as January 1 or January 12 depending on your system. Always use DATE(2024,1,1) to avoid ambiguity.
  • Counting across multiple columns: COUNTIFS requires each criteria range to be the same size. If your dates are in A and B, use COUNTIFS(A:A,...)+COUNTIFS(B:B,...) or restructure data.

COUNTIF vs. COUNTIFS: When to Use Each

Use COUNTIF when you have exactly one condition, such as counting all dates after a certain day. Use COUNTIFS when you have two or more conditions, which is necessary for a year range. COUNTIFS is available in all modern Excel versions (2010 and later). For older versions like Excel 2003, you'd need SUMPRODUCT.

If you need to count by year and also by another criterion (e.g., sales region), add that as another criteria range:

=COUNTIFS(A2:A100,">="&DATE(2024,1,1),A2:A100,"<"&DATE(2025,1,1),B2:B100,"West")

This counts dates in 2024 where column B equals “West”.

Alternatives: PivotTables and Dynamic Arrays

For a quick visual count, create a PivotTable: drag the date field to Rows, then group by Years (right-click > Group > Years). The PivotTable will show a count per year automatically. This is often easier for exploratory analysis.

If you have Excel 365, you can use FILTER and ROWS:

=ROWS(FILTER(A2:A100,(A2:A100>=DATE(2024,1,1))*(A2:A100

This returns the number of rows in the filtered array. Note that FILTER requires Excel 2021 or Microsoft 365.

Frequently Asked Questions

How do I count dates in the current year without updating the formula?

Use =SUMPRODUCT(--(YEAR(A2:A100)=YEAR(TODAY()))). This compares each date's year to the current year, so it automatically updates every January 1.

Why does my COUNTIFS return 0 even though I have dates?

Most likely your dates are stored as text or your criteria use text strings that Excel doesn't recognize as dates. Check by selecting a date cell and looking at the formula bar – if it shows a date like 1/15/2024, it's a real date. If it shows text, convert it. Also ensure your criteria use DATE() or a cell reference with a real date.

Can I count dates in a fiscal year that starts in July?

Yes, adjust the DATE boundaries. For a fiscal year July 1, 2023 to June 30, 2024, use =COUNTIFS(A2:A100,">="&DATE(2023,7,1),A2:A100,"<"&DATE(2024,7,1)).

Does COUNTIFS count hidden rows or filtered cells?

No, COUNTIFS ignores rows hidden by filtering or manually hidden. It only counts visible cells if the range is filtered. If you need to count hidden rows too, use SUBTOTAL or a helper column with a formula, but that's more complex.

What if my dates include times and I want to count by day?

Use INT to strip times in a helper column, then apply COUNTIFS on that column. For example, put =INT(A2) in B2 and drag down, then count B:B for the year.

Final Tips for Reliable Year Counting

  • Always use DATE() in criteria to avoid locale issues.
  • Test with a small sample to verify the count matches your manual count.
  • If you use SUMPRODUCT, ensure no text or error cells in the range.
  • For large datasets, COUNTIFS is faster than SUMPRODUCT.

With these formulas, you can accurately count dates within any year, calendar or rolling, and adapt to fiscal years or additional criteria.

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.