COUNTIFS Excel Error? How to Find and Fix Common Causes

A COUNTIFS error is usually caused by criteria ranges with different dimensions, invalid criteria syntax, or references that no longer work. First check the exact error shown, then confirm every criteria range is the same size and each criterion is written or referenced correctly; if the formula returns 0 rather than an error, it may simply have found no exact matches.

What COUNTIFS Does

COUNTIFS counts rows or cells that meet multiple conditions at the same time. It is useful for questions such as “How many paid orders came from the West region?” or “How many dates fall in this period?” Each condition is supplied as a pair: a range to check and a criterion to apply to that range. Excel evaluates the pairs together using AND logic, so a counted row must satisfy every condition.

For example, with regions in A2:A6 and order statuses in B2:B6, assume the rows contain West/Paid, West/Open, East/Paid, West/Paid, and East/Open. The formula below counts rows where the region is West and the status is Paid. It returns 2 because two rows meet both conditions.

With that sample layout, enter:

=COUNTIFS(A2:A6,"West",B2:B6,"Paid")

COUNTIFS is available in current Microsoft Excel and has been included in Excel since Excel 2007. Its result is a number, not a list of matching records. A result of 0 is a valid answer, not itself an error.

Start by Identifying the Error

The message or result narrows the likely cause. Check the formula bar as well as the cell display: a formula may be correct but return zero because the source data does not match the criteria exactly.

#VALUE! often points to criteria ranges that have different row or column dimensions. Check for a range that starts or ends one row earlier than the others.
#NAME? commonly means Excel does not recognize a name in the formula. Check the function spelling, named ranges, and whether text criteria are enclosed in quotation marks.
#REF! indicates a broken reference, often because a referenced row, column, sheet, or range was deleted.
A result of 0 usually means the formula evaluated successfully but found no rows matching all criteria. Investigate spaces, data types, dates, and the criteria itself.
A visible string such as #VALUE! inside a cell may be text imported from another source rather than an error produced by the formula. The distinction matters when diagnosing the underlying data.

Excel versions and workbook settings can affect how a formula is interpreted, but the error message is a more useful starting point than changing random parts of the formula. If the formula was copied from a website or another spreadsheet, also check whether its argument separators match your Excel settings.

Make Every Criteria Range the Same Size

All criteria ranges in one COUNTIFS formula must have matching dimensions. If one range covers rows 2 through 20 and another covers rows 2 through 19, the pairs do not describe corresponding records consistently and Excel can return #VALUE!. The same principle applies to columns: ranges need the same number of rows and columns.

With region values in A2:A20 and status values in B2:B20, both ranges have 19 cells. If a third condition uses dates, its range should also cover exactly the corresponding 19 rows, such as C2:C20. Using A:A, B:B, and C:C is also dimensionally consistent, although full-column references can make a workbook slower when used repeatedly in complex models.

Check the range boundaries carefully after copying a formula. A common slip is extending one criterion range to row 500 while leaving another at row 499. Different starting rows are not automatically wrong if the ranges have the same dimensions, but the cells at matching positions are evaluated together. For example, A2:A20 is paired position-by-position with B5:B23, which may be syntactically compatible but logically misaligned if the data records begin on the same row.

When a formula uses Excel Tables, structured references can reduce accidental range-size differences because the columns expand with the table. Confirm that the referenced columns cover the same records and that any filters are not being mistaken for COUNTIFS conditions: COUNTIFS evaluates the referenced cells, including rows hidden by a filter.

Check the Function Syntax and Criteria

The general structure is COUNTIFS followed by one or more criteria-range and criterion pairs. A missing argument, misplaced comma, unmatched parenthesis, or misspelled function name can prevent Excel from evaluating the formula. Excel’s formula bar and function argument prompts can help reveal which part is missing.

Text criteria written directly in a formula need straight double quotation marks. A criterion such as West without quotation marks may be treated as a name, leading to #NAME? if Excel cannot find a defined name called West. Text can also be supplied through a cell reference, which avoids typing the word into the formula each time.

With regions in A2:A6, statuses in B2:B6, the desired region in D2, and desired status in E2, enter:

=COUNTIFS(A2:A6,D2,B2:B6,E2)

If D2 contains West and E2 contains Paid, the result is 2 for the sample data described earlier. This formula is useful when a report user should be able to change the criteria in cells without editing the formula.

For numeric comparisons, the comparison operator must be part of the criterion. When the threshold is stored in another cell, join the operator and cell reference with the ampersand. When the operator is fixed, place the complete comparison in quotation marks. A criterion such as ">=100" means greater than or equal to 100; it is not the same as the text value “>=100” in a data cell.

In some regional configurations Excel uses semicolons rather than commas as the list separator. If a correctly structured formula pasted from elsewhere is rejected at entry, check your local Excel separator setting. The formulas in this article use commas, as requested for standard Excel formula syntax.

Understand Wildcards and Exact Matches

Text matching is a frequent reason a formula returns zero even though the values look alike. COUNTIFS generally compares text without distinguishing uppercase from lowercase, but spaces and other characters still matter. “Paid” and “Paid ”, with a trailing space, are different values. Imported data may also contain nonbreaking spaces or other invisible characters that ordinary visual inspection misses.

For partial text matching, Excel supports wildcards in criteria: an asterisk (*) stands for any number of characters, a question mark (?) stands for one character, and a tilde (~) escapes a wildcard when you need to match it literally. For instance, a criterion containing *west* can match text that includes “west” anywhere within the cell. Wildcards apply to text matching; they are not a substitute for numeric comparisons.

To investigate hidden spaces, compare a suspicious cell with a clean sample, or use Excel’s trimming and cleanup tools in a helper column. Be aware that TRIM removes ordinary extra spaces but does not remove every nonbreaking space imported from web pages or other systems. Cleaning the source data is often more reliable than making a complicated criterion to work around inconsistent text.

Handle Numbers and Dates Correctly

Numbers stored as text can look identical to numeric values while behaving differently in comparisons. A cell containing the text “100” is not necessarily treated like the number 100. Check whether values are left-aligned by default, carry a warning indicator, or fail to respond to numeric formatting as expected. Converting the source column to real numbers can resolve a zero count or an unexpected comparison result.

Dates need similar care. Excel stores genuine dates as serial numbers and displays them using date formats. Text that resembles a date may not be a real date value, and date interpretation can vary with regional settings. When counting a date interval, use real dates in the data and criteria cells, and use comparison criteria rather than relying on a display format.

With dates in A2:A6 and a genuine Excel date in D1, where D1 is January 15, 2026, enter:

=COUNTIFS(A2:A6,">="&D1)

Assuming the five cells contain January 4, January 9, January 15, January 22, and February 3, 2026, the result is 3. The formula counts January 15 and the two later dates. The ampersand joins the comparison operator to the date value in D1; it does not convert text dates in A2:A6 into genuine dates.

For a range with both a starting and ending date, add another pair for the same date column, using a greater-than-or-equal criterion for the start and a less-than-or-equal criterion for the end. Make sure the endpoints are real dates and that the time component is understood if the cells contain date-and-time values. A value at noon on the final day is later than midnight at the start of that day, which can affect boundary conditions.

Why a Correct Formula Can Still Return Zero

Zero is often a data-quality or criteria mismatch rather than a formula error. Before rewriting the formula, check whether all criteria are intended to apply to the same record. COUNTIFS does not count a row that matches only some of its conditions. For instance, a row with region West and status Open does not count when the formula asks for West and Paid.

Confirm that the selected data includes the rows you expect and does not stop just before newer records.
Compare the criterion with the actual cell contents, including spaces, punctuation, and spelling.
Check whether numeric-looking values or dates are stored as text.
Temporarily test one condition at a time, then add the other criteria back in pairs. This helps identify which condition eliminates the matches.
Check whether the formula uses the intended AND logic. Adding another criteria pair makes the requirements stricter; it does not create an either/or condition.

If the intended question is “West or East,” putting both values into separate criteria pairs for the same region range would require a cell to be both values at once, so it will not express the intended alternative. OR logic requires a different construction, such as adding counts for distinct conditions, with care to avoid counting the same record twice when conditions overlap.

When to Use Another Approach

COUNTIFS is a good choice for a straightforward count of records that satisfy several conditions. It is easier to audit when each pair is clearly aligned and the criteria are visible in cells. For a count based on only one condition, COUNTIF is the simpler related function. For totals rather than counts, SUMIFS applies multiple criteria to a sum range.

If the task needs a list of matching records rather than a count, COUNTIFS is not the right output function. In Microsoft 365 or Excel 2021 and later, FILTER can return rows meeting conditions; older Excel versions may require a filter, helper column, or another approach. A PivotTable can also summarize counts by categories without writing a separate formula for every combination. Choose based on whether the desired result is a single count, a total, or a filtered set of records.

For large workbooks, avoid unnecessary full-column references in thousands of formulas if calculation becomes slow. A properly sized Excel Table is often easier to maintain because its structured references adjust as records are added. Performance depends on the workbook and the number of formulas, so replacing a valid formula purely because it uses full columns is not always necessary.

Frequently Asked Questions

Why does COUNTIFS return #VALUE?
Check that every criteria range has the same number of rows and columns. Also inspect references for malformed or broken ranges. Mismatched dimensions are a common cause of this error.

Why does COUNTIFS return 0 when I can see matching values?
The displayed values may differ in ways that are hard to notice: extra spaces, text instead of numbers, text instead of real dates, or a criterion that does not match the intended rows. Test each condition separately and inspect the underlying values.

Does COUNTIFS distinguish uppercase and lowercase?
No. COUNTIFS text criteria are generally case-insensitive. It can still distinguish text that differs by spaces, punctuation, or other characters, so visually similar values may not match.

Can COUNTIFS check two different values as OR conditions?
Each criteria pair adds a condition that must be true, so pairs normally work as AND conditions. For OR logic, count the alternatives separately and add them when the groups do not overlap, or use a method designed for the required logic.

Can COUNTIFS count dates between two days?
Yes. Use two criteria pairs against the same date range: one for dates on or after the start and one for dates on or before the end. Use genuine Excel date values and consider whether the source cells include times.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.