COUNTIFS Function Not Working? Fix Common Errors

If COUNTIFS is not working, check that every criteria range has the same size and shape, then verify that each criterion matches the underlying cell values and uses valid syntax. Also check for text stored as numbers, date boundaries, and manual calculation mode; these are frequent reasons a formula returns zero or an unexpected count.

How COUNTIFS works

COUNTIFS counts rows or cells that meet every condition you supply. Its arguments alternate between a range to check and the condition to apply to that range. For example, it can count records where a status is Paid and a region is East. The conditions are joined with AND logic: a record counts only when it satisfies all the specified conditions.

With status values in A2:A6 and regions in B2:B6, the formula below counts rows that are both Paid and East. Assume the five rows contain Paid/East, Paid/West, Pending/East, Paid/East, and Paid/East, respectively; the result is 3.

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

COUNTIFS is not the same as adding separate counts for each condition. If you want either condition to be true, you need an OR approach instead. Excel supports COUNTIFS in current Microsoft 365 for Windows, as well as many earlier desktop versions. The basic function is available in Excel 2007 and later.

Check the ranges and argument pairs

Each criteria range must correspond in size and shape to the other criteria ranges. If the first range is A2:A100, use ranges covering the same rows for the other conditions, such as B2:B100. Mixing A2:A100 with B2:B99 can produce a #VALUE! error. A horizontal range and a vertical range are not interchangeable either.

Make sure every range has a criterion beside it. A missing criterion, an extra comma, or a misplaced closing parenthesis can make Excel reject the formula or interpret it differently than intended. COUNTIFS accepts up to 127 range-and-criterion pairs, but a normal formula usually needs only a few.

Check that each range starts and ends on the intended rows.
Confirm that every criteria range has the same number of rows and columns.
Look for accidentally omitted arguments or unmatched parentheses.
When copying a formula, inspect references that may have shifted. Use dollar signs, as in $A$2:$A$100, when a range should stay fixed.

Whole-column references such as A:A and B:B can be convenient and have matching dimensions, but formulas over many large ranges may recalculate more work than necessary. If a workbook becomes slow, try bounded ranges or an Excel Table with structured references. Keep the ranges aligned when you make that change.

Write criteria with the right syntax

For a text criterion, put the text in double quotes inside the formula. A criterion can also be a cell reference, in which case it is usually written without quotes. Text matching in COUNTIFS is not case-sensitive, so Paid and PAID normally match the same entries. Leading or trailing spaces are different characters, however, and can cause a seemingly identical label not to match.

Comparison operators such as greater than or equal to must be included in a text criterion. When the threshold is stored in another cell, join the operator to that cell reference with an ampersand. With order totals in C2:C6, the values 120, 85, 200, 150, and 75, this formula counts totals of at least 100 and below 200. It returns 2, for 120 and 150.

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

The less-than symbol in the displayed formula is an HTML-escaped character; in Excel, enter the ordinary < character. If the threshold is in F1 and C2:C6 contains the values above, use this formula to count amounts at least as high as F1. If F1 contains 100, the result is 4.

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

In the HTML display, > and & represent the normal greater-than sign and ampersand. In Excel, enter those characters directly. A common error is putting the cell reference inside the quotes, which makes Excel treat it as literal text rather than reading the cell value.

Fix date and number mismatches

Excel stores real dates as serial numbers and displays them using a date format. A value that merely looks like a date but is stored as text may not match a numeric date criterion. If a date count unexpectedly returns zero, check a sample cell’s value and format, and confirm that the source values are actual Excel dates. Changing the display format alone does not convert text into a date.

For a month-based count, use an inclusive start date and an exclusive start date for the next month. With actual dates in E2:E4—March 1, 2026; March 15, 2026; and April 2, 2026—the following formula returns 2. The exclusive upper limit also handles dates that include times, such as March 31 at 3 p.m.

=COUNTIFS(E2:E4,">="&DATE(2026,3,1),E2:E4,"<"&DATE(2026,4,1))

For numbers, look for values imported as text. A cell can display 100 but still contain text, especially after importing a CSV or copying data from another system. Check alignment and use a test or conversion method appropriate to the data before changing a large range. Also check whether a criterion includes an unintended space or currency symbol. Number formatting usually changes how a number looks, not its underlying value.

Use wildcards carefully

COUNTIFS supports wildcards in text criteria. An asterisk (*) represents any number of characters, while a question mark (?) represents exactly one character. A tilde (~) makes the following wildcard character literal. These are useful for matching patterns, but they can also make a criterion broader than expected.

With customer names in D2:D5 containing Northwind, Northwind Traders, Southwind, and Northwind Ltd, respectively, the formula below returns 3 because it counts names beginning with North.

=COUNTIFS(D2:D5,"North*")

To count only the exact text Northwind, use the criterion without a wildcard. If the text you need to match contains a literal asterisk or question mark, escape it with a tilde; otherwise, Excel interprets it as a pattern. For instance, a criterion intended to match a literal asterisk uses ~* within the quoted criterion.

Understand AND, OR, and blank criteria

Multiple criteria pairs in one COUNTIFS formula use AND logic. To count records that meet either of two alternatives in the same column, add separate counts, taking care not to count overlapping categories twice. With regions in B2:B6 containing East three times and West once, this formula returns 4 for the total of those two groups.

=COUNTIFS(B2:B6,"East")+COUNTIFS(B2:B6,"West")

For more complex OR conditions across several columns, write down the combinations you want to include before building the formula. If a row can satisfy more than one combination, simply adding counts may count it twice. In that case, the formula needs a method that counts unique rows, or the data may be easier to analyze with a helper column, a PivotTable, or another suitable approach.

Blank criteria can also be confusing. A criterion of an empty string can count blank cells, but cells containing formulas that return an empty string may behave differently from genuinely empty cells in some comparisons. Conversely, criteria such as <> may count cells with content that is not visibly obvious, including spaces. If blank-related totals look wrong, inspect the cells and distinguish true empty cells from formulas and whitespace.

Troubleshoot unexpected results in Excel

If the formula is accepted but returns zero or a count that seems wrong, use a small, controlled check rather than rewriting everything at once. Confirm one condition first, then add the next condition. This helps identify which criterion or range changes the result.

Click the formula cell and check that Excel recognizes the expression as a formula, not text. A leading apostrophe or a cell formatted as Text can prevent calculation.
Temporarily test one criteria pair at a time. If a single condition returns the expected count but the combined formula does not, check the other condition and its matching range.
Compare criteria with the actual data, including punctuation, spaces, hidden characters, and whether numbers or dates are stored as text.
Check for a leading equals sign, balanced parentheses, valid quotation marks, and the correct argument separator for your regional settings. Many installations use commas; some regional settings require semicolons instead.
Check calculation mode. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. If the workbook is set to Manual, choose Calculate Now on the Formulas tab to recalculate.

Excel for Mac has the same COUNTIFS logic, although ribbon layout and keyboard behavior can differ from Windows. Excel for the web also supports COUNTIFS, but some editing controls may appear in a different location. If a formula works on one device but not another, compare regional list separators and workbook calculation settings before assuming the function itself changed.

COUNTIFS versus COUNTIF and other options

Use COUNTIF when one range and one condition are enough; use COUNTIFS when all of several conditions must be met. COUNTIFS can also handle a single condition, so there is no need to add an unnecessary second pair. The key distinction is practical: COUNTIF has one criteria range, while COUNTIFS is designed for multiple criteria pairs.

A PivotTable can be a better choice when you need to explore totals by several categories, change groupings frequently, or share an interactive summary. A helper column can make complicated row-level logic easier to inspect and explain. For simple counts across a few clearly defined conditions, COUNTIFS is often more direct and automatically updates as the source values change, subject to calculation settings.

Do not expect COUNTIFS to perform case-sensitive text matching: it treats differences in letter case as equivalent. If case matters, use a different approach designed for case-sensitive comparison. Likewise, if criteria involve complex OR logic, multiple possible matches, or data-quality cleanup, a single COUNTIFS formula may be the wrong tool rather than a broken one.

Frequently Asked Questions

Why does COUNTIFS return 0 when the value is visibly there?

The cell may contain extra spaces, a different punctuation mark, a number or date stored as text, or a value that does not satisfy every condition. Check each criteria pair separately and inspect the underlying cell value, not just its displayed appearance.

Why does COUNTIFS show a #VALUE! error?

A common cause is criteria ranges with different dimensions, such as A2:A100 and B2:B99. Align the ranges so they cover the same number of rows and columns, and check that the formula’s arguments and parentheses are complete.

Can COUNTIFS count dates in a particular month?

Yes. Use a criterion for dates on or after the first day of the month and another for dates before the first day of the next month. This boundary method includes all times on dates in the month, provided the source cells contain real Excel date values.

Does COUNTIFS distinguish uppercase and lowercase text?

No. COUNTIFS text criteria are not case-sensitive. If the count must distinguish between, for example, East and EAST, use a case-sensitive method instead of relying on COUNTIFS alone.

Why does my formula use semicolons instead of commas?

Excel uses the list separator set by regional settings. Many installations use commas, while some use semicolons. If Excel rejects a formula copied from another region, replace the separators as required by your installation while keeping the argument order unchanged.

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.