Function Problems

How to Use COUNTIFS with a Current-Year Date Range in Excel

Algirdas Jasaitis Sep 27, 2026 869 views

Question details

The user needs an Excel formula to count matching date values within the current year, dynamically choosing between two different columns depending on the text present in a specific cell.

How to Use COUNTIFS with a Current-Year Date Range in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic date-based reports where the formula automatically counts data between January 1 and December 31 of the current year, conditionally evaluating column F or H.
Observed behavior: The formula successfully applies the date boundaries for the current year and correctly checks column F if the condition is met, or column H otherwise.

Before you start

Ensure your target columns contain recognized date formats rather than text strings, as Excel's COUNTIFS function requires valid serial date values to process greater-than and less-than logical operators correctly.

Solution 1Recommended

Combine IF and COUNTIFS with Dynamic Date Functions

Use the IF function paired with COUNTIFS, YEAR, TODAY, and DATE functions to dynamically count current-year data across conditionally selected columns.

By utilizing the TODAY() and YEAR() functions, you eliminate the need to manually update your formula each year. The IF function first determines which column range needs to be evaluated based on your specified condition.

Select the target cell

Click on the cell in your worksheet where you want the final count result to be displayed.

Enter the formula

Input the following formula into the formula bar: =IF(E13="One Time",COUNTIFS($F$13:$F$446,">="&DATE(YEAR(TODAY()),1,1),$F$13:$F$446,"<="&DATE(YEAR(TODAY()),12,31)),COUNTIFS($H$13:$H$446,">="&DATE(YEAR(TODAY()),1,1),$H$13:$H$446,"<="&DATE(YEAR(TODAY()),12,31)))

Execute the calculation

Press Enter to calculate the result. The formula checks if E13 contains 'One Time'; if true, it counts dates in column F that fall within the current year. If false, it counts dates in column H.

Combine IF and COUNTIFS with Dynamic Date Functions

Operator Syntax: Always remember to enclose the logical operators (">=" and "<=") in quotation marks and use the ampersand (&) to concatenate them with the DATE function.

Solution 2

Use Basic COUNTIFS for Current Year Only

If you do not need conditional column checking, you can use a simplified COUNTIFS formula for a single column.

Spreadsheet Solutions

Calculate Dynamic Dates Easily in WPS Spreadsheet

You can effortlessly build, test, and manage complex formulas like dynamic COUNTIFS in WPS Spreadsheet. It provides a robust formula editor and full compatibility with Microsoft Excel functions.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
2. Select a cell for your formula: Click on the target cell and begin typing your COUNTIFS formula.
3. Utilize formula suggestions: Use the formula bar's auto-complete feature to quickly select the TODAY() and DATE() functions with correct syntax.
4. Execute the formula: Press Enter to instantly execute the calculation and view your dynamic date count.

100% compatible with Microsoft Excel formulas and date functionsBuilt-in formula auto-completion to prevent syntax errorsLightweight application with an intuitive, familiar interfaceFree to use for daily data analysis and reporting tasks

Frequently Asked Questions

Why is my COUNTIFS formula returning zero instead of the correct date count?

This usually happens if your dates are stored as text rather than numerical date values. Select your date column, go to the 'Data' tab, and use the 'Text to Columns' wizard to convert them into standard date formats that the formula can read.

Can I use COUNTIF instead of COUNTIFS for a date range?

COUNTIF only supports a single condition. To check for a date range, you must define a minimum start date and a maximum end date, which requires two conditions. Therefore, you must use COUNTIFS, which handles multiple criteria.

How do I count dates for the previous year dynamically?

You can adjust the formula to subtract 1 from the YEAR function. Your criteria would change to ">="&DATE(YEAR(TODAY())-1,1,1) and "<="&DATE(YEAR(TODAY())-1,12,31).

Does the TODAY() function update automatically?

Yes, TODAY() is a volatile function. It automatically updates to the current system date every time you open the workbook or whenever the sheet recalculates, ensuring your current-year filter remains accurate year after year.

Excel & Data Problems