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,
COUNTIFSwon't compare them correctly. Convert text to dates usingDATEVALUEin a helper column, or use--A2:A100in SUMPRODUCT if the text is a recognizable date format. - Blank cells or errors:
COUNTIFSignores blanks, butSUMPRODUCTwithYEARwill return #VALUE! if any cell has an error. Wrap withIFERRORor 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 useDATE(2024,1,1)to avoid ambiguity. - Counting across multiple columns:
COUNTIFSrequires each criteria range to be the same size. If your dates are in A and B, useCOUNTIFS(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,
COUNTIFSis faster thanSUMPRODUCT.
With these formulas, you can accurately count dates within any year, calendar or rolling, and adapt to fiscal years or additional criteria.
Related
- Excel Auto Loan Amortization Template: How to Build One
- Excel Audit Process: Step-by-Step Guide
- Excel Audit Log Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- EDATE in Excel: Use It for Months, Not DaysExcel’s EDATE function moves a date forward or backward by a number of months, not by a number…
- Excel COUNTIF Range Contains Text: Formula GuideYes, you can use COUNTIF to count cells that contain text, but the exact formula depends on what…
- XLOOKUP Multiple Criteria: Formula and ExamplesYes. In Excel for Microsoft 365 or Excel 2021 and later, XLOOKUP can match multiple criteria by multiplying…
- Excel Formula for Extracting Text from a CellTo extract a set number of characters in Excel, use =LEFT(A2,3) when the text is in A2 and…
- Excel TEXTSPLIT Function Syntax, Examples, and TipsIn Excel for Microsoft 365, the split function is TEXTSPLIT, with the syntax TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode],…
- How to Match Names Across Two Excel Spreadsheets QuicklyTo find names from one Excel spreadsheet in another, put the names to check in column A and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.