COUNTIFS Formula Not Working? Diagnose and Fix the Errors

If COUNTIFS is returning 0, an unexpected count, or an error, check that every criteria range is the same size, then verify the criteria match the underlying data exactly. In Excel for Microsoft 365 on Windows, the function counts rows only when every condition is true; small differences in spaces, dates, number formats, or formula syntax can make a match fail.

How COUNTIFS works

COUNTIFS evaluates one or more range-and-criteria pairs. A row is counted only when the value in each specified range meets its corresponding condition. The function is therefore an AND test across criteria: if you ask for region East and sales of at least 100, a row must satisfy both tests.

With regions in A2:A6 and sales in B2:B6, where the rows are East/120, East/80, West/150, East/100, and West/90, use =COUNTIFS(A2:A6,"East",B2:B6,">=100"). The result is 2: the first and fourth rows meet both conditions. The function name is COUNTIFS, with an S, and each criteria range must be followed by its criterion.

COUNTIFS is available in current desktop Excel, including Microsoft 365. It is different from COUNTIF, which checks one condition. Use COUNTIFS even for one condition if you expect to add more later, but COUNTIF is also valid for a single test.

Check the formula structure and range sizes

A common cause of an error is mismatched range dimensions. The ranges need not start in the same column, but they must cover the same number of rows and columns. For example, A2:A100 and B2:B100 are compatible; A2:A100 and B2:B99 are not. A mismatch can produce #VALUE! or a result that does not reflect the intended records.

Confirm each range-and-criterion pair is present and separated by commas.
Make sure text criteria are inside straight double quotation marks, such as “East”.
Use a colon for a range, as in A2:A100, rather than a comma between its endpoints.
Check that the opening and closing parentheses are balanced.
When copying the formula, check whether relative references shifted. Use dollar signs to lock a range if you will fill the formula across or down.

For example, with categories in A2:A20 and amounts in B2:B20, a criterion entered in E2 and a threshold in F2, use =COUNTIFS(A2:A20,E2,B2:B20,">"&F2). If E2 contains East and F2 contains 100, the formula counts East rows with amounts greater than 100. The ampersand joins the comparison operator to the value in F2.

Make criteria match the cell contents

Text that looks identical can still differ. A cell may contain a leading or trailing space, a nonbreaking space copied from a website, or a different spelling. COUNTIFS does not generally distinguish uppercase from lowercase, so East and EAST match, but extra spaces and other characters matter. Inspect a suspect cell in the formula bar, or compare its length with a known-good value using LEN.

Wildcards help when the exact text is not known. An asterisk matches any number of characters, and a question mark matches one character. A tilde escapes a wildcard when you need to find the literal character. For example, with descriptions in A2:A20 and statuses in B2:B20, use =COUNTIFS(A2:A20,"*north*",B2:B20,"<>Closed") to count rows whose description contains north and whose status is not Closed. The result depends on the data; the pattern is not limited to a word at the start of a cell.

To count cells that are not blank, use the criterion “<>”; to count blank cells, use “”. Be mindful that a formula returning an empty string may appear blank but is not always treated the same as a truly empty cell in every counting scenario. If blank handling matters, test the formula on a few known rows.

Fix number and date criteria

Numbers that are stored as text are a frequent source of a zero count. A cell displaying 100 may actually contain the text “100”, particularly after importing a CSV or pasting from another system. Check alignment and the formula bar, or test a representative cell with ISNUMBER. Converting the source values to real numbers is usually more reliable than trying to compensate with a complicated criterion.

Comparison operators must be included in quotation marks when typed directly, or joined to a cell reference with &. With amounts in B2:B20, this counts values at least 100: =COUNTIFS(B2:B20,">=100"). With amounts in B2:B20 and a threshold in F2, use =COUNTIFS(B2:B20,">="&F2). The second formula changes its threshold when F2 changes.

Dates should be real Excel date values, not text that merely looks like a date. To count dates in a calendar year, use a start-inclusive and end-exclusive interval. With dates in A2:A100, this formula counts dates from January 1, 2026 through December 31, 2026: =COUNTIFS(A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2027,1,1)). It works even if the cells contain times, because every time on December 31 is earlier than midnight on January 1, 2027. The year here is an example; change it to the period you need.

Understand why a result is zero or too high

A zero usually means at least one condition excludes every row, not necessarily that the formula is broken. Temporarily test each condition by itself with COUNTIF or a smaller COUNTIFS formula. If the individual tests return matches but the combined test returns zero, the conditions may not occur together on the same rows, or one range may be misaligned.

A result that is too high often points to a broad wildcard, duplicate records, or a criterion that is less restrictive than intended. For instance, “*East*” can match a longer label such as Northeast. If you need an exact category, use “East” without wildcards. Also confirm that the formula includes the full intended data range but does not include headings or unrelated rows.

COUNTIFS applies AND logic between its pairs, not OR logic. To count rows that are either Open or Pending in a single status column, with statuses in A2:A6, use =COUNTIF(A2:A6,"Open")+COUNTIF(A2:A6,"Pending"). If categories can overlap, adding two counts may count a row twice; design the tests so they are mutually exclusive or account for the overlap.

Check calculation, separators, and workbook settings

If the formula is correct but the displayed answer seems stale, check calculation mode. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. In Manual mode, Excel may not recalculate every dependent formula immediately; press F9 to recalculate. On Mac, use the Formulas tab and its Calculation Options controls where available; menu placement can vary by Excel release.

The examples here use commas between arguments, as requested for standard English-language Excel syntax. Some regional configurations use semicolons as the list separator. If Excel rejects a formula that otherwise looks correct, check the separator expected by your installation and replace argument-separating commas with semicolons as appropriate. Do not replace commas inside quoted text unless they are actually part of the criterion.

Also check whether the cell is displaying the formula as text. If so, the cell may have Text number format, or the formula may begin with an apostrophe. Change the format to General, remove any leading apostrophe, and re-enter the formula. If every formula on the sheet is visible instead of its result, turn off Show Formulas from the Formulas tab.

Use a dependable troubleshooting sequence

Work from the simplest test to the full formula rather than rewriting everything at once. This makes it easier to identify which condition is responsible.

Click the formula cell and inspect the formula bar for missing quotes, parentheses, or criteria pairs.
Confirm the criteria ranges are equal in size and point to the intended rows.
Test one condition at a time, then add the other conditions back individually.
Check example source cells for extra spaces, text-formatted numbers, text dates, and inconsistent labels.
Verify the comparison operator and any wildcard are intentional.
Check calculation mode and the regional argument separator if Excel shows an error or stale result.

When the data is imported repeatedly, consider cleaning it at the source or using a helper column to normalize values. TRIM can remove ordinary leading and trailing spaces from text, but it does not remove every invisible character; CLEAN can remove some nonprinting characters. For a one-off count, adjusting the source cells is often safer than making the criteria increasingly complex.

COUNTIFS compared with alternatives

COUNTIFS is a good fit when the task is to count records that meet several straightforward conditions. Use COUNTIF for one condition, or SUMIFS when you want to add values from a sum range using multiple criteria. A PivotTable can be more useful when you need to summarize many categories, compare totals, or let other people filter the results interactively.

For unusual logic, such as OR conditions across different fields or criteria that depend on calculations, a helper column can make the rule visible and easier to audit. Dynamic-array formulas may also help in Microsoft 365, but they are not necessary for ordinary multi-condition counts. Choose the simplest method that accurately expresses the rule and can be maintained by the workbook’s users.

Frequently Asked Questions

Why does COUNTIFS return 0 when the values look the same?
Check for spaces or hidden characters, numbers or dates stored as text, and criteria that do not match the exact cell contents. Test a known row and each condition separately.

Can COUNTIFS count rows that meet either condition?
COUNTIFS combines its criteria with AND logic. For OR logic, add separate COUNTIF or COUNTIFS results when the tests are mutually exclusive, or use a helper column or another approach if they overlap.

Does COUNTIFS treat uppercase and lowercase as different?
No. Its text matching is generally case-insensitive. It does still require the relevant characters and spaces to match, unless you use wildcards.

Why does COUNTIFS show #VALUE!?
Check that paired criteria ranges have matching dimensions and that the formula has valid syntax. A mismatched range size is a common cause.

Can I use a cell reference as the criterion?
Yes. Put the reference in the criterion argument. For a comparison such as greater than a value in another cell, join the operator and reference with &, as shown above.

Why does my formula use semicolons instead of commas?
Excel’s argument separator can depend on regional settings. Use the separator your installation expects; the function logic remains the same.

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.