Function Problems

How to Use Excel COUNTIFS Formula with Number and Date Criteria

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to count records based on two conditions: one column must contain a value greater than 22500, and another column must contain a date of birth on or after January 5, 2003.

Product: Excel
Device & OS: not provided
Scenario: Filtering and counting rows in a dataset that must meet both a numerical threshold and a specific date condition.
Observed behavior: The goal is to properly structure the COUNTIFS formula so that the spreadsheet correctly evaluates both the numerical logic and the date criteria without interpreting the date as plain text.

Before you start

Ensure that the column containing your dates is formatted as actual date values and not as plain text, otherwise the logical comparison will fail to calculate correctly.

Solution 1Recommended

Use COUNTIFS Combined with the DATE Function

Combine the COUNTIFS function with the DATE function to ensure your date criteria are evaluated accurately regardless of regional date formats.

Using the DATE function inside your COUNTIFS criteria prevents errors caused by varying regional date formats (such as MM/DD/YYYY versus DD/MM/YYYY). To make this work, you must enclose the logical operator in quotation marks and concatenate it to the DATE function using an ampersand (&).

Select the output cell

Click on the empty cell where you want the final counted number to appear.

Input the COUNTIFS formula

Type =COUNTIFS(A:A, ">22500", B:B, ">="&DATE(2003,1,5)) into the formula bar. Be sure to replace A:A with your actual numerical column range and B:B with your date column range.

Adjust your specific criteria

Modify the 22500 and the DATE(2003,1,5) values to match your specific numerical threshold and target date (Year, Month, Day).

Calculate the result

Press the Enter key. The cell will now display the total count of records that successfully meet both conditions.

Check Date Formatting: If your formula unexpectedly returns 0, verify that your date column contains real dates. Select the column, right-click, choose 'Format Cells', and apply a valid Date format.

Efficient Data Analysis

Calculate Complex Criteria Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced Excel functions, including COUNTIFS and DATE, allowing you to filter and analyze your data efficiently. It offers an intuitive interface for complex formulas and is fully compatible with Microsoft Excel files.

1. Open your dataset in WPS: Launch WPS Spreadsheet and open your workbook containing the data you want to analyze.
2. Format data columns: Highlight your columns, right-click, and ensure your number column is formatted as Numbers and your date column as Dates.
3. Apply the COUNTIFS formula: In an empty cell, input =COUNTIFS(Range1, ">Number", Range2, ">="&DATE(YYYY,M,D)) and press Enter to instantly get your record count.

Fully compatible with Microsoft Excel (.xlsx) formatsSupports all standard Excel formulas including COUNTIFS, SUMIFS, and DATEUser-friendly interface for managing large datasets easilyFree and lightweight alternative for daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return zero when comparing dates?

This usually happens if the dates in your range are stored as text strings rather than actual Excel date values. It can also occur if the date criteria in your formula is incorrectly formatted as a text string instead of using the DATE function.

How do I format the DATE function inside COUNTIFS?

The DATE function requires the syntax DATE(year, month, day). When combining it with a logical operator like '>=', you must wrap the operator in quotation marks and join it to the DATE function with an ampersand, such as: ">="&DATE(2003,1,5).

Can I add a third criteria to the COUNTIFS formula?

Yes, the COUNTIFS function can handle up to 127 range/criteria pairs. Simply append a comma after the second criteria and add your third range followed by another comma and its specific criteria.

Excel & Data Problems