Excel COUNTIFS Not Working? Fix Common Formula Errors

When Excel COUNTIFS is not working, first check that every criteria range has the same shape, then confirm that the criteria actually match the cell values and that Excel is recalculating. A result of 0 usually means no rows meet all the conditions; an error or unchanged result points more often to a range, syntax, or calculation issue.

How COUNTIFS works

COUNTIFS counts rows or cells only when every supplied condition is true. It uses AND logic across its criteria pairs: a row must meet the first condition, the second condition, and any others. For instance, a sales list can be counted for one region and a minimum amount at the same time. COUNTIFS is available in current Excel versions, including Microsoft 365.

With regions in A2:A6 and sales amounts in B2:B6, where the rows contain North/120, North/80, South/150, North/120, and South/200, this formula counts North sales of at least 100. It returns 2 because two North rows meet the amount test:

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

Each criteria range is paired with a criterion. The criterion can be text, a number, a comparison such as greater than, a wildcard pattern, or a reference to a cell containing the condition. The order matters: the first range goes with the first criterion, the second range with the second criterion, and so on.

Check range size and alignment first

All criteria ranges in one COUNTIFS formula need the same number of rows and columns. A range such as A2:A100 cannot be paired with B2:B99: the ranges do not describe the same set of records. Excel may return an error, or the formula may count a different set of rows than you intended if ranges are offset but happen to have equal dimensions.

Check these details before changing the criteria:

Every range starts on the same data row and ends on the same data row.
Each range covers the same number of columns as well as rows.
The ranges exclude headings unless the heading cells are intentionally part of the data.
When copying a formula, use dollar signs to keep fixed ranges from shifting, such as $A$2:$A$100.

For example, with category data in A2:A6 and status data in B2:B6, those ranges line up row by row. If one range instead starts in row 3, the formula compares a category from one record with a status from another. That can produce a plausible-looking but incorrect count, which is harder to spot than an error message.

Understand criteria and comparison operators

Criteria must be written in a form Excel can interpret. Text criteria go in straight double quotation marks. Comparison operators such as >, >=, <, and <> are usually included inside the quoted criterion. A common mistake is placing a comparison operator outside the quotation marks or leaving out the quotation marks around text.

When a criterion is stored in a cell, join the operator to the cell reference with the ampersand. With regions in A2:A6, sales amounts in B2:B6, the text North in D2, and the minimum amount 100 in E2, the formula below returns 2 for the example data above:

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

The ampersand joins the text operator to the value in E2, creating a valid criterion such as >=100. Without that joining step, Excel may treat the operator and cell reference as separate pieces instead of one condition. Also check for extra spaces in criterion cells: a cell containing North followed by a space is not identical to North.

Numbers stored as text and extra spaces

A cell can look like a number while being stored as text. This can happen after importing a CSV, pasting data from a website, or entering an apostrophe before a value. Text and numeric values can behave differently in criteria matching, so a count that should include a displayed value such as 100 may be unexpectedly low. Check the cell alignment and number format, but do not rely on appearance alone; formats can make text look like numbers.

To investigate, select a sample value and inspect the formula bar, or test the data in a spare cell with Excel’s ISNUMBER function. If the source is meant to be numeric, convert the column consistently rather than patching the COUNTIFS criterion. Depending on the data, you can use the warning menu that appears for numbers stored as text, or use Data > Text to Columns and complete the conversion. Keep a copy of imported data before applying a bulk cleanup.

Spaces and invisible characters cause a related problem with text. A leading space, trailing space, or nonbreaking space copied from a web page can make two labels that look alike compare differently. TRIM removes ordinary extra spaces, but it does not remove every nonbreaking space. Clean a helper column, verify the cleaned values, and then count against that consistent column rather than assuming the labels match by sight.

Dates need to be real Excel dates

Excel stores valid dates as serial numbers and displays them using a date format. A date imported as text may look correct but fail a date comparison. Confirm that the cells behave as dates by changing their number format temporarily to General or by checking a sample with ISNUMBER. If the underlying value is text, convert it before relying on date boundaries.

For dates in C2:C5 containing January 3, January 31, February 1, and January 15, 2026, this formula counts the three January dates. It uses an inclusive start and an exclusive start of the following month, so times attached to dates are handled safely too:

=COUNTIFS(C2:C5,">="&DATE(2026,1,1),C2:C5,"<"&DATE(2026,2,1))

The result is 3. This boundary approach is often more reliable than trying to match a displayed month name or a date formatted as text. If dates are displayed in a different regional order, such as day/month/year instead of month/day/year, DATE avoids ambiguity in the formula itself. The source cells still need to contain valid Excel dates.

Wildcards, blanks, and case sensitivity

For text criteria, an asterisk matches any number of characters and a question mark matches one character. With product names Green Tea, Green tea bags, Iced Tea, and Coffee in A2:A5, the following formula returns 3 because each of the first three names contains Tea:

=COUNTIFS(A2:A5,"*Tea*")

COUNTIFS text matching is not case-sensitive, so Tea and tea both match that pattern. To search for a literal asterisk or question mark rather than use it as a wildcard, precede the character with a tilde. A blank criterion can count empty cells, while a not-blank criterion can count cells containing an entry; check whether formulas returning an empty string should be treated as blank for your purpose.

Another frequent misunderstanding is expecting multiple criteria for one range to mean OR. COUNTIFS applies conditions together, not as alternatives. To count either North or South in A2:A6, where the example list contains three North rows and two South rows, add two separate counts; the result is 5:

=COUNTIFS(A2:A6,"North")+COUNTIFS(A2:A6,"South")

That method is clear when the categories are distinct. If the alternatives overlap, adding counts can count the same record more than once, so define the intended logic before combining results.

When the result is zero, an error, or does not update

A zero is a valid result, not necessarily a broken function. It means Excel found no records satisfying all criteria. Test one condition at a time to locate the mismatch: temporarily use a COUNTIF for a single condition, then add the next criteria pair. Compare the actual cell contents with the criterion, including spaces, punctuation, number type, and date type.

If Excel displays an error, inspect the formula for missing arguments, mismatched quotation marks, or criteria ranges with different dimensions. In some regional settings, Excel uses semicolons rather than commas as the list separator. If a pasted formula using commas is rejected, use the separator expected by that installation; the function name and logic are otherwise the same.

If the formula appears in the cell instead of calculating, the cell may be formatted as Text or the formula may have a leading apostrophe. Set the cell format to General, edit the formula, and press Enter. Also check whether Show Formulas is enabled; the Windows shortcut is Ctrl+` (the grave-accent key), and it toggles formula display. On Mac, the equivalent shortcut is Control+` in many Excel configurations; keyboard layouts can vary.

If the result remains unchanged after source data changes, check Formulas > Calculation Options > Automatic. In manual calculation mode, Excel does not recalculate every formula after each edit. Press F9 on Windows to calculate, or use the equivalent calculation command for your Mac keyboard and Excel version. Avoid switching to manual calculation unless the workbook genuinely needs it, because other formulas can also appear stale.

COUNTIFS compared with COUNTIF and alternatives

Use COUNTIF when there is one condition and COUNTIFS when the count depends on two or more conditions. COUNTIFS is usually easier to maintain than nesting several separate tests because the ranges and criteria appear in pairs. For OR logic across distinct categories, adding separate COUNTIFS results is a straightforward option; for more complicated logic, a helper column can make the rule easier to audit.

For a quick diagnostic, use filters to view the rows that should qualify and compare the visible records with the formula result. A PivotTable can be more useful when the goal is to summarize many categories repeatedly. Neither alternative automatically fixes inconsistent source data, though: incorrect dates, hidden spaces, or mixed number types can affect filtered results and summaries too.

Frequently Asked Questions

Why does COUNTIFS return 0 when I can see matching values?
Check for extra or nonbreaking spaces, numbers stored as text, text-formatted dates, and a criterion that does not match the actual value. Also confirm that all criteria apply to the same rows and that every condition is true for at least one record.

Can COUNTIFS use OR conditions?
Its criteria pairs use AND logic. For alternatives in one field, add separate COUNTIFS results when the alternatives do not overlap. If they overlap, account for duplicate matches or use a helper column to express the rule clearly.

Why does COUNTIFS show an error?
Check that all criteria ranges have matching dimensions, each range has a corresponding criterion, and quotation marks and parentheses are balanced. If Excel rejects the formula as entered, your regional settings may require semicolons rather than commas.

Does COUNTIFS distinguish uppercase and lowercase?
No. COUNTIFS text criteria are case-insensitive, so criteria for North and NORTH match the same text values. If case-sensitive counting is essential, use a different approach designed to compare case.

Why does my COUNTIFS result not change after editing data?
Check Formulas > Calculation Options > Automatic, then recalculate if needed. Also verify that the edited cells are inside the ranges referenced by the formula and that the formula was not entered as text.

A reliable troubleshooting order

Start by confirming the intended logic, then inspect range alignment, criteria syntax, and the underlying data types. Test each condition separately, check for stray characters, and finally verify calculation settings. This order helps distinguish a genuinely empty match from a formula error, while avoiding unnecessary edits to a formula that is already applying the requested conditions correctly.

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.