COUNTIFS Formula Not Working? Fix Common Errors

If COUNTIFS is not working, first check that every criteria range is the same size and that each range is followed by the criterion you intend to test. Then check how the criteria are written, whether the data contains hidden spaces or text-formatted numbers, and whether Excel is recalculating the formula.

How COUNTIFS works

COUNTIFS counts rows or cells that meet multiple conditions at the same time. In Excel for Microsoft 365 on Windows, its syntax is =COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...). Each criteria range and criterion is a pair: Excel tests the first range against the first criterion, the second range against the second criterion, and so on. A row is counted only when all the conditions are true.

For example, if column A contains product names and column B contains regions, a COUNTIFS formula can count records where the product is Pen and the region is North. The result is an intersection of the conditions, not the total of either condition separately. This AND behavior is one of the most common sources of confusion: adding another pair to COUNTIFS makes the test more restrictive.

All criteria ranges must have matching dimensions. They can be on different worksheets, but they should cover corresponding rows and columns. A range of A2:A100 paired with B2:B99 is mismatched and can return an error. Keep headers out of the ranges unless you deliberately want to test them, and use consistent starting and ending rows.

Check the range-and-criteria pairs

A simple way to find a structural mistake is to read the formula from left to right and label each pair. The first argument is a range, the next is its criterion; then the pattern repeats. If a range is accidentally supplied where a criterion should be, or a criterion is paired with the wrong column, Excel may return an unexpected count even though the formula is syntactically valid.

With product names in A2:A6 and regions in B2:B6, where rows 2 and 5 are Pen in North, this formula returns 2:

With product names in A2:A6 and regions in B2:B6, use:

=COUNTIFS(A2:A6,"Pen",B2:B6,"North")

Check every referenced range in the formula bar. If the data grows regularly, using an Excel table can reduce the risk of ranges ending on different rows: select the data and choose Insert > Table, then build the formula using the table’s column names. Structured references expand as table rows are added. If you use ordinary cell references instead, update all paired ranges together when new records are appended.

Write criteria for text, numbers, and operators

Text criteria are usually enclosed in straight double quotation marks, as in "North". A criterion that tests a numeric threshold can also use quotation marks when it contains a comparison operator. With sales amounts in C2:C6 containing 80, 120, 100, 95, and 140, the following returns 3 because three values are at least 100:

With sales amounts in C2:C6, use:

=COUNTIFS(C2:C6,">=100")

For a threshold stored in a cell, join the operator to the cell reference with the ampersand. With sales amounts in C2:C6 and the threshold in E2, use:

=COUNTIFS(C2:C6,">="&E2)

That formula counts values greater than or equal to E2. Without the ampersand, Excel does not combine the operator with the value in the cell as intended. For exact numeric matching, a number can be used as the criterion directly; for criteria containing operators, use the quoted operator text and concatenate a cell reference when needed.

COUNTIFS text matching is not case-sensitive, so Pen and PEN are treated as a match. Wildcards can help when text varies: an asterisk represents any number of characters, and a question mark represents one character. With descriptions in A2:A100, this counts entries that begin with Pen:

With descriptions in A2:A100, use:

=COUNTIFS(A2:A100,"Pen*")

If a cell value contains a literal asterisk, question mark, or tilde and you want to match that character rather than use it as a wildcard, prefix it with a tilde in the criterion. Also check the separator between formula arguments. This article uses commas for standard English-language Excel settings; some regional settings use semicolons, so a formula copied from another locale may need its separators changed.

Look for spaces and inconsistent data types

A value that looks identical on screen may not match because the stored content differs. A product name might contain a trailing space, a nonbreaking space copied from a webpage, or a different spelling. A cell may also contain a number stored as text instead of a numeric value. These issues are especially common in data pasted from exports, forms, or websites.

Click a suspicious cell and inspect its contents in the formula bar; look for extra spaces or unexpected characters.
Compare the source values with the criterion cell, including spelling, punctuation, and spaces.
Check whether numbers are consistently stored as numbers. A warning indicator or inconsistent alignment can be a clue, though formatting alone does not prove the underlying type.
Clean or convert source data in a helper column when possible, then point COUNTIFS at the cleaned values.

A blank criterion and a criterion containing a space are not the same. With entries in A2:A20, this counts cells Excel treats as blank:

With entries in A2:A20, use:

=COUNTIFS(A2:A20,"")

A criterion containing one space instead searches for a space character. Formulas that return an empty string can also make a range appear blank, so consider whether the cells are truly empty or contain formulas. If the count remains surprising, inspect a few individual cells rather than relying only on their displayed appearance.

Handle dates as date values, not visual text

Dates often cause trouble because Excel stores valid dates as serial numbers but displays them using a date format. Data imported as text can look like a date without behaving like one. Confirm that the source dates are real Excel date values, and check that the cells used for date criteria also contain valid dates.

For a month-long count, using an inclusive start date and an exclusive next-month boundary is often safer than trying to match a date displayed with a particular format. With dates in D2:D100, the first day of the target month in H2, and the first day of the following month in I2, use:

=COUNTIFS(D2:D100,">="&H2,D2:D100,"<"&I2)

This counts dates on or after H2 and before I2. It includes every time on each date within the month if the source cells contain date-and-time values, while excluding the next month’s first instant. If you instead set the upper condition to less than or equal to a date at midnight, later times on that final date may be missed. Make sure H2 and I2 are actual dates, not text that merely looks like one.

Understand common errors and surprising results

A wrong count does not always produce an Excel error message. If the formula returns zero, the syntax may be valid while no row meets every condition. If it returns too many or too few, investigate the data and criteria before rewriting the whole formula.

#VALUE!: Check that paired criteria ranges have matching dimensions and that references are valid.
#NAME?: Check that COUNTIFS is spelled correctly, the formula begins with an equals sign, and text criteria have quotation marks.
Zero when matches exist: Check spaces, spelling, date types, number types, and whether the criteria accidentally describe an AND condition that no row satisfies.
A count that does not change: Check calculation settings and confirm the formula is referencing the cells that changed.
A formula displayed as text: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then re-enter the formula.

Excel’s calculation setting is on the Formulas tab under Formulas > Calculation Options. If it is set to Manual, formulas may not update automatically after source data changes. Choose Automatic when appropriate, then recalculate. Also check for a leading apostrophe or a leading space if Excel displays the formula itself rather than its result.

Use a practical troubleshooting sequence

Changing several parts of a formula at once makes it harder to identify the cause. Instead, reduce the problem to the smallest test that should work, then add conditions back one at a time.

Confirm that the formula cell is not showing the formula as plain text and that the formula starts with an equals sign.
Test one condition by itself with COUNTIF, or temporarily remove the other range-and-criteria pairs. Confirm the basic match count first.
Check each range’s first and last row, then verify that every paired range has the same shape.
Inspect the criterion exactly as Excel evaluates it: text, operator, number, date, wildcard, or cell reference.
Check a matching-looking source cell for extra characters and for numbers or dates stored as text.
Add the remaining criteria pairs back one at a time. The pair that makes the result change unexpectedly points to the problem.
Check calculation mode and recalculate if the result appears stale.

This sequence also helps distinguish a formula error from a data-quality problem. If a single-condition test already returns zero, the issue is likely in that condition or its source values. If each condition works separately but the combined formula returns zero, the conditions may simply not occur together on any one row.

When to use COUNTIF or another approach

COUNTIFS is the right choice when all listed conditions must be true. For a single condition, COUNTIF is shorter and serves the same basic purpose. For alternatives where either condition may be true, two COUNTIFS results can be added, but only if the groups do not overlap; otherwise overlapping rows are counted twice. For a more complex OR test, consider a helper column or a formula designed for that logic rather than assuming another COUNTIFS pair means OR.

For instance, counting records for either North or South is not achieved by adding both criteria to one COUNTIFS formula, because that would ask one region cell to equal North and South at the same time. A helper column, separate counts, or another carefully designed formula can express the alternative condition. When using separate counts, check whether a record could satisfy both groups before summing them.

Excel tables are another useful alternative to manually maintained ranges, not a different counting function. Tables help keep references aligned as records are added. On Excel for Mac, the COUNTIFS function and its general behavior are the same; ribbon labels and keyboard shortcuts can differ slightly from Windows. Excel for the web also supports COUNTIFS, though the interface and available workbook features can vary.

Frequently Asked Questions

Why does COUNTIFS return zero even though I can see matching values?
Check for extra spaces, spelling differences, text-formatted numbers, and dates stored as text. Also verify that all conditions can be true on the same row; COUNTIFS applies AND logic across its criteria pairs.

Can COUNTIFS use different-sized ranges?
Use criteria ranges with matching dimensions. Pairing ranges with different row or column counts can produce an error or an unreliable setup. Align their start and end points so each row is tested against its corresponding row.

How do I count dates in a month?
Use a lower boundary for the first day of the month and an exclusive upper boundary for the first day of the next month. This approach handles date-and-time entries more reliably than relying on how dates are displayed.

Does COUNTIFS distinguish uppercase and lowercase text?
No. COUNTIFS text criteria are not case-sensitive. If capitalization must determine whether a record counts, use a different approach that explicitly tests case.

Why does my COUNTIFS result not update?
Check Formulas > Calculation Options and make sure calculation is not set to Manual. Also confirm that the changed cells are within the referenced ranges and that the formula has been recalculated.

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.