Why Are My COUNTIFS Not Working? Fix the Common Causes

COUNTIFS usually fails because a criterion does not match the cell values exactly, its ranges are misaligned, or the formula’s references point somewhere unexpected. Check the criteria and range sizes first; then verify dates, text, wildcards, and calculation settings before rebuilding the formula.

How COUNTIFS is supposed to work

COUNTIFS counts rows or cells that meet multiple conditions at the same time. Its arguments come in pairs: a range to check, followed by the condition to apply to that range. Each additional pair adds another condition, and all conditions must be true for a record to be counted. This is AND logic, not OR logic.

For example, with employee names in A2:A100 and regions in B2:B100, the formula below counts rows where the name is Maya and the region is East. If the sample data contains two such rows, the result is 2.

=COUNTIFS(A2:A100,"Maya",B2:B100,"East")

COUNTIFS is not case-sensitive: Maya and MAYA normally match the same text. It can count text, numbers, dates, blanks, and cells meeting comparison rules, but the criteria must be written in a form Excel can interpret. A formula that is syntactically valid can still return zero if its conditions do not match the underlying data.

Check the criteria and argument pairs

Count the arguments in the formula and make sure every criteria range has a corresponding criterion. A missing argument or misplaced comma can make Excel report an error or evaluate a different condition than intended. In Excel for Microsoft 365 on Windows, click the formula bar and inspect the pairs from left to right; selecting a function in the formula bar can also expose its argument fields.

Comparison operators must be included inside a text criterion. To count values greater than 100, use the criterion ">100", not a bare >100. When the comparison value is in a cell, join the operator to the cell reference with the ampersand. With sales figures in C2:C20 and a threshold in F1, this formula returns the number of sales greater than the value in F1; if F1 is 100 and three values exceed 100, the result is 3.

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

Use quotation marks around text criteria and around criteria that contain an operator. A plain numeric criterion can be entered as a number or cell reference. Also check whether a criterion cell contains an extra space, a different spelling, or a formula result that is not what it appears to be.

Make every criteria range the same size

All criteria ranges in one COUNTIFS formula must have the same number of rows and columns. A formula combining A2:A100 with B2:B99 has mismatched dimensions and can return #VALUE!. Even when Excel accepts the references, inconsistent starting or ending rows can make the formula compare the wrong records.

Choose a consistent data area and apply it to every range. If names are in A2:A100 and regions are in B2:B100, both references should cover rows 2 through 100. Avoid mixing a whole-column reference such as A:A with a partial range such as B2:B100; use matching whole columns or matching bounded ranges instead. Bounded ranges can also be easier to audit and may be more efficient in large workbooks.

For a changing list, an Excel Table can reduce range-maintenance mistakes. Select the data, choose Insert > Table, and use the table’s column references in formulas. When you add rows to the table, its structured references expand with the data. This is useful when a formula seems to stop counting newly added records because its original range ends too soon.

Fix date and time criteria

Excel stores real dates as serial numbers, even though cells display them in a familiar date format. A date criterion may fail if the source contains text that looks like a date rather than a genuine Excel date. Formatting a text value to look like a date does not convert its underlying value. Check a few source cells, and, if needed, convert the imported data into real dates before relying on date comparisons.

For a date interval, use a start boundary and an exclusive end boundary. Assume B2:B5 contains four real date-and-time values: April 1, April 30, May 1, and April 15, 2026. Put April 1, 2026, in D1 and May 1, 2026, in E1. This formula counts dates on or after the start and before the end; it returns 3, including all three April records and excluding May 1.

=COUNTIFS(B2:B5,">="&D1,B2:B5,"<"&E1)

The exclusive end is especially helpful when the source includes times. A condition of less than May 1 includes every time on April 30, whereas a condition of less than or equal to a midnight value on April 30 would omit later times on that day. If dates are stored as text, first convert them consistently rather than trying to solve the mismatch by changing their display format.

Understand wildcards, blanks, and text mismatches

In text criteria, an asterisk (*) matches any sequence of characters, and a question mark (?) matches one character. If C2:C5 contains INV-101, INV-205, PO-101, and ABC, this formula counts the two values beginning with INV-. The asterisk allows any characters after the hyphen.

=COUNTIFS(C2:C5,"INV-*")

If you need to match an asterisk or question mark that is actually part of the cell’s text, put a tilde before it to treat it literally. For instance, the criterion "~*" looks for a literal asterisk. A frequent mistake is using a wildcard when the desired condition is an exact match, or expecting a partial match without adding a wildcard.

Blank criteria can also be surprising. An empty-string criterion, written as "", is commonly used to count blank-looking cells; cells containing formulas that return an empty string can also appear blank to COUNTIFS. Conversely, a cell with a space is not truly empty. If the result seems off, inspect the cell contents in the formula bar, and consider whether imported spaces or formulas are affecting what you see.

Numbers stored as text and numbers stored as numeric values may look identical on the sheet but behave differently in comparisons. Cell formatting alone does not make text numeric. Look for the green error indicator, test a small sample, or convert the source column consistently. Similarly, extra leading or trailing spaces in text can prevent an expected exact match.

Check references, filters, and calculation

When a formula is copied down or across, relative references shift. If the criteria should always come from F1 and G1, lock those references with dollar signs. Assume names are in A2:A100, regions in B2:B100, and F1 and G1 contain Maya and East. The formula below returns 2 if two rows meet both conditions, and the locked criteria references stay fixed when the formula is copied.

=COUNTIFS(A2:A100,$F$1,B2:B100,$G$1)

COUNTIFS counts matching cells even when their rows are hidden or excluded by a filter. Filtering a list does not make COUNTIFS count visible rows only. If you expected the count to change when you filtered the data, that expectation—not necessarily the formula—is the problem; a visible-rows-only count needs a different approach.

If the displayed answer is stale after editing data, check whether calculation is set to Manual. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and select Automatic. Also confirm that the formula cell is not formatted as Text and that the entry begins with an equals sign. If Excel displays the formula instead of its result, change the format to General and re-enter the formula.

Use a quick troubleshooting sequence

Work through these checks in order; each one isolates a common cause without changing the data unnecessarily:

Confirm the formula begins with =COUNTIFS( and that each criteria range is paired with a criterion.
Compare the dimensions of all criteria ranges, including their first and last rows.
Test each condition separately by temporarily using a simpler COUNTIFS formula or checking a few matching records.
Inspect criteria cells and source values for spaces, spelling differences, text-versus-number issues, and dates stored as text.
Review operators, wildcards, and cell references. Lock references that should not move when the formula is copied.
Check whether filters, hidden rows, or Manual calculation explain the result you see.

Excel’s formula separators can depend on regional settings. The examples here use commas, as in a typical English-language configuration. If Excel rejects a formula that otherwise looks correct, your installation may use semicolons between arguments; use the separator shown by formulas already working in that copy of Excel.

COUNTIFS versus COUNTIF and other approaches

Use COUNTIF when one condition is enough, and COUNTIFS when you need multiple conditions that must all be true. COUNTIFS is often clearer than nesting several tests because each range and criterion is visible as a pair. It does not provide a special OR mode, however. For example, counting East or West requires a different construction, and adding two separate counts can double-count records if the conditions overlap.

For criteria that are difficult to express, a helper column can make the logic easier to inspect: calculate a clear label or flag for each row, then count that result. This is often more maintainable than forcing a complicated condition into one formula. If the goal is to count only visible rows, or to apply complex logic, choose a method designed for that requirement rather than assuming COUNTIFS will respond to filters or interpret the criteria differently.

Frequently Asked Questions

Why does COUNTIFS return 0 when I can see matching values?
Check for extra spaces, text stored as numbers, dates stored as text, or a criterion that does not match the underlying value. Also verify that the formula points to the rows containing those values and that every condition is true for the same row.

Why does COUNTIFS return #VALUE?
A common cause is criteria ranges with different dimensions, such as A2:A100 paired with B2:B99. Make the ranges the same height and width, then review the formula for missing or misplaced arguments.

Does COUNTIFS ignore filtered or hidden rows?
No. It counts matching cells in its ranges whether their rows are visible or hidden. Filtering a table does not restrict its count to visible records.

Can COUNTIFS count dates between two dates?
Yes. Use a greater-than-or-equal condition for the start date and a less-than condition for the day after the interval. This approach includes times throughout the final day while excluding the next day.

How do I count cells that contain part of a word?
Use wildcards in the text criterion: an asterisk matches any sequence, while a question mark matches one character. For example, "INV-*" matches text beginning with INV-.

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.