Formula Errors

How to Fix COUNTIFS Formula Errors for Date Ranges in Excel

WPS Editor Sep 25, 2026 869 views

Question details

The user needs to accurately count records based on department names and specific date ranges using the COUNTIFS function, but the formula is returning incorrect results.

How to Fix COUNTIFS Formula Errors for Date Ranges in Excel

Product: Excel
Device & OS: not provided
Scenario: Counting data rows imported from an MS Forms CSV file that match a specific department within a designated fiscal year or date range.
Observed behavior: The COUNTIFS formula returns incorrect counts (often zero) because of extra spaces in the text criteria, unconcatenated comparison operators for dates, or date columns being formatted as text.

Before you start

Before modifying your formulas, check your source data to ensure that dates imported from CSV or MS Forms are formatted as actual date values rather than text strings.

Solution 1Recommended

Format Date Criteria and Remove Text Spaces Correctly

Update the COUNTIFS syntax to separate the comparison operator from the DATE function or cell reference, and use TRIM to clean text inputs.

A common mistake when using COUNTIFS with dates is placing the comparison operator inside the DATE function or failing to concatenate it. You must enclose operators like >= in quotes and join them to the date using an ampersand (&). Additionally, imported text often contains hidden trailing spaces, which the TRIM function can resolve.

Select the result cell

Click on the cell where you want to display the final calculated count.

Structure the date comparison

Type the formula structure separating operators from the dates, such as ">="&DATE(2024,4,1) for the start date and "<="&DATE(2025,3,31) for the end date.

Apply TRIM to text criteria

Wrap your department text criteria reference with the TRIM function to remove extra spaces, for example: TRIM(A2).

Combine and execute the formula

Enter the complete formula: =COUNTIFS('Training request form data'!I:I,TRIM(A2),'Training request form data'!G:G,">="&DATE(2024,4,1),'Training request form data'!G:G,"<="&DATE(2025,3,31)) and press Enter.

Format Date Criteria and Remove Text Spaces Correctly

Using Cell References for Dates: Instead of hardcoding the DATE function, you can place your start and end dates in specific cells (like B2 and C2) and refer to them dynamically: ">="&B2 and "<="&C2.

Solution 2

Convert Imported Text Dates to True Excel Dates

Fix source data formatting issues where dates imported from a CSV are stored as text, preventing COUNTIFS from evaluating them correctly.

Fix Formula Errors Easily with WPS Office

WPS Spreadsheet offers powerful formula auditing tools and seamless compatibility with Microsoft Excel functions like COUNTIFS, DATE, and TRIM. You can easily manage, clean, and analyze data from CSV imports for free.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your CSV or Excel workbook containing the source data.
2. Enter the formula: Select your destination cell and type the =COUNTIFS formula.
3. Follow the syntax prompts: Use the smart formula bar prompts to accurately input TRIM for text and concatenate (&) your date comparisons.
4. Calculate the result: Press Enter to instantly calculate your multi-criteria counts across the dataset.

Fully compatible with Microsoft Excel formulas, functions, and file formats.Built-in Error Checking to easily identify syntax mistakes in complex COUNTIFS formulas.Fast processing and automatic formatting of large CSV imports from MS Forms.Free, lightweight, and features a familiar user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS return 0 when comparing dates?

This usually happens if the comparison operators are inside the date string or not concatenated properly. You must enclose operators in quotes and use an ampersand, like ">="&DATE(2024,4,1) or ">="&B2. It also occurs if your source data dates are stored as text instead of numerical date values.

How can I ignore trailing spaces in COUNTIFS criteria?

You can wrap your cell criteria with the TRIM function, for example, =COUNTIFS(Range, TRIM(A2)). Alternatively, you can use wildcards like A2&"*" to count cells that start with a specific word but might have hidden characters at the end.

Can I use cell references for date ranges in COUNTIFS?

Yes. You can place your start date in one cell (e.g., B2) and your end date in another (e.g., C2). In your formula, replace the DATE function with the cell references by typing ">="&B2 for the start criteria and "<="&C2 for the end criteria.

Excel & Data Problems