Excel COUNTIFS Year Equals: How to Count by Year

Formulas & functions · Updated November 22, 2026

The direct answer is to use the COUNTIFS function with a date range and a condition that checks if the year equals a specific value, like this: =COUNTIFS(A2:A100,">=1/1/2024",A2:A100,"<=12/31/2024"). This counts all cells in A2:A100 that fall within the year 2024, assuming those cells contain real Excel dates.

Why COUNTIFS with Dates Needs Two Conditions

In Excel, dates are stored as serial numbers (e.g., 1/1/2024 is 45292). A single condition like "=2024" won't work because it would look for a date that literally equals the number 2024, not a date in the year 2024. To capture an entire year, you must set a lower boundary (January 1) and an upper boundary (December 31). The COUNTIFS function accepts multiple criteria ranges and conditions, so you use two pairs: one for the start and one for the end.

Using the YEAR Function Inside COUNTIFS

If you prefer a single condition, you can use the SUMPRODUCT function with the YEAR function: =SUMPRODUCT(--(YEAR(A2:A100)=2024)). This works by converting each date to its year and then comparing to 2024. The double unary (--) converts TRUE/FALSE to 1/0 so SUMPRODUCT can sum them. However, COUNTIFS is generally faster on large datasets because it is optimized for counting. Another option is to add a helper column with =YEAR(A2) and then use COUNTIF on that column, but that adds extra steps.

Step-by-Step: Build the COUNTIFS Formula

  1. Assume your dates are in column A, rows 2 to 100.
  2. Click an empty cell, for example C1.
  3. Type =COUNTIFS(A2:A100,">=1/1/2024",A2:A100,"<=12/31/2024").
  4. Press Enter. Excel will count all dates from Jan 1, 2024 to Dec 31, 2024 inclusive.

If you want to make the year a variable, put the year number in a cell (e.g., D1 = 2024) and use: =COUNTIFS(A2:A100,">="&DATE(D1,1,1),A2:A100,"<="&DATE(D1,12,31)). The DATE function builds a valid date from the year, month, and day.

How to Count by Year When Dates Are Text

If your dates are stored as text (e.g., "2024-03-15"), the above formula will not work because text dates are not recognized as dates. First, convert them to real dates using the DATEVALUE function or Excel's Text to Columns feature (Data > Text to Columns > Delimited > Next > Next > Date > MDY). After conversion, the COUNTIFS formula will work. If you cannot convert, you can use SUMPRODUCT with LEFT: =SUMPRODUCT(--(LEFT(A2:A100,4)="2024")) assuming the text always starts with the year in YYYY format. This is a workaround, not a native date solution.

Common Mistakes and How to Fix Them

  • Using a single condition: =COUNTIFS(A2:A100,2024) returns 0 because no date equals the number 2024. Always use two conditions.
  • Wrong date format: If your system uses DD/MM/YYYY, the formula ">=1/1/2024" might be interpreted as January 1 or January 2024? Actually Excel interprets based on your regional settings. To avoid ambiguity, use DATE(2024,1,1) inside the formula.
  • Including blank cells: Blank cells are ignored by COUNTIFS, but if you have text in the range, it may cause #VALUE! errors if you use SUMPRODUCT with YEAR. Use =SUMPRODUCT((YEAR(A2:A100)=2024)*1) which treats text as zero, but if text is present, YEAR returns #VALUE! error. To handle, use =SUMPRODUCT((ISNUMBER(A2:A100))*(YEAR(A2:A100)=2024)).
  • Using whole column references: A:A works but can slow down calculation. Use a specific range like A2:A1000.

Counting by Year with Additional Criteria

COUNTIFS shines when you need multiple conditions. For example, to count sales in 2024 for the "North" region (region in B2:B100), use: =COUNTIFS(A2:A100,">=1/1/2024",A2:A100,"<=12/31/2024",B2:B100,"North"). You can add as many criteria pairs as needed. This is a common use case in business reports. For counting by fiscal year (e.g., July to June), adjust the start and end dates accordingly: =COUNTIFS(A2:A100,">=7/1/2023",A2:A100,"<=6/30/2024").

Alternatives: PivotTables and Dynamic Arrays

If you need to count by multiple years at once, a PivotTable is more efficient. Select your data, go to Insert > PivotTable, drag the date field to Rows, then right-click and choose Group > Years. This automatically groups dates by year without formulas. For Excel 365, you can use the FILTER function with YEAR: =ROWS(FILTER(A2:A100,YEAR(A2:A100)=2024)). This returns the count of rows that meet the condition. Both methods are valid, but COUNTIFS is simpler for a single year.

Frequently Asked Questions

Can I use COUNTIF with a year condition?

No, COUNTIF only handles one condition. You need COUNTIFS for two conditions (start and end of year). Alternatively, use SUMPRODUCT with YEAR.

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

Check if your dates are actually text. Use the ISDATE function (in Excel 365) or format the cells as Date. Also, ensure your criteria use the correct date format for your region. Using DATE(2024,1,1) avoids regional issues.

How do I count dates in the current year?

Use =COUNTIFS(A2:A100,">="&DATE(YEAR(TODAY()),1,1),A2:A100,"<="&DATE(YEAR(TODAY()),12,31)). This updates automatically each year.

Can I count by year and month at the same time?

Yes, add two more criteria: for month start and end. For example, count March 2024: =COUNTIFS(A2:A100,">=3/1/2024",A2:A100,"<=3/31/2024").

What if my dates include times? For example, 1/15/2024 14:30.

COUNTIFS with date boundaries works fine because time is a decimal fraction of the day. The condition ">=1/1/2024" includes all times on that date. To exclude times on Dec 31, use "<1/1/2025" instead of "<=12/31/2024".

Is there a way to use COUNTIFS with a year extracted from a date?

You can use =COUNTIFS(A2:A100,">="&DATE(2024,1,1),A2:A100,"<"&DATE(2025,1,1)). This is the same as using Dec 31 but avoids the last day issue. Using < with the next year's Jan 1 is a common best practice.

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.