Why COUNTIFS Is Not Working Properly in Excel

COUNTIFS usually gives an unexpected result because a criterion does not match the cell values as Excel stores them, the criteria are written incorrectly, or the criteria ranges do not have matching dimensions. Check those three things first, then confirm whether you intended AND logic, OR logic, and the treatment of dates, blanks, and wildcards.

What COUNTIFS does

COUNTIFS counts rows where every supplied condition is true. It is useful for questions such as “How many orders are paid and from the East region?” Each criteria range is checked position by position: the first cell in every range belongs to one record, the second cells belong to another, and so on. A record is counted only when all its tests pass.

For example, with regions in A2:A6—East, West, East, East, West—and order statuses in B2:B6—Paid, Paid, Pending, Paid, Pending—the following counts East-region orders with a Paid status. It returns 2 because rows 2 and 5 meet both conditions.

With regions in A2:A6 and statuses in B2:B6 as described above, use =COUNTIFS(A2:A6,"East",B2:B6,"Paid"). The expected result is 2.

This “all conditions must match” behavior is the first important distinction to understand. COUNTIFS is not a general-purpose OR function: adding another range-and-criterion pair makes the test more restrictive, not more inclusive.

Check the criteria syntax

COUNTIFS takes alternating range and criterion arguments: first a range to inspect, then the condition for that range, followed by another range and condition if needed. Put text criteria in quotation marks. Comparison operators such as greater than or less than must also be inside the criterion text.

A fixed condition such as greater than 100 can be written as ">100". If the threshold is in a cell, join the operator to the cell reference with the ampersand. Without that join, Excel may treat the criterion as text that does not express the intended comparison.

With regions in A2:A6, amounts in C2:C6 (120, 120, 80, 150, 90), and the numeric threshold 100 in F1, this formula counts East-region records above the threshold. It returns 2: the amounts 120 and 150 belong to East records and exceed 100.

With regions in A2:A6, amounts in C2:C6, and 100 in F1, use =COUNTIFS(A2:A6,"East",C2:C6,">"&F1). The expected result is 2.

In the HTML formula above, the visible formula should be entered in Excel with ordinary characters: =COUNTIFS(A2:A6,"East",C2:C6,">"&F1). Excel’s actual comparison operator is the greater-than symbol (>), and the ampersand joins it to F1. If copying from a webpage turns characters into entities, type those two characters directly in Excel.

Also check for quotation marks copied from formatted documents. Excel formulas require straight double quotation marks, not curly “smart quotes.” In some regional settings, Excel uses semicolons instead of commas between arguments. The examples here use commas; if Excel rejects a formula at entry, your regional list-separator setting may require semicolons.

Make sure ranges line up

Every criteria range in one COUNTIFS formula must have the same number of rows and columns as the first criteria range. For ordinary column-based data, keep all ranges the same height and start and end them on the same rows. A range such as A2:A100 paired with B2:B99 does not describe aligned records and can return a #VALUE! error.

For instance, using A:A for the region range and B2:B100 for the status range gives the ranges different dimensions. Avoid mixing whole-column ranges with partial ranges. Either use corresponding bounded ranges, such as A2:A100 and B2:B100, or use whole-column references consistently where appropriate.

Do not include a heading in only one criteria range. If a header is in row 1, make each range start in row 2. Likewise, if your data has been filtered, remember that COUNTIFS evaluates cells in the referenced ranges; it does not automatically limit its count to visible rows. A filtered-out record can still satisfy the criteria.

When the formula returns #VALUE!, range-size mismatch is a strong possibility. When it returns a plausible but incorrect number, check alignment: a range that starts one row too early may compare a person’s status with someone else’s region without producing an error.

Check what the cells actually contain

A cell’s appearance is not necessarily its underlying value. A number formatted as text can look identical to a real number, and a date displayed as “Jan 1” may be a serial number, text, or a date-time value. COUNTIFS compares stored values, so formatting alone does not make unlike values equivalent.

Numbers stored as text: A criterion for numeric 100 may not match a text entry containing “100.” Check how the source data was imported or entered, and convert values consistently if necessary.
Extra spaces: “Paid” and “Paid ” are different text values for an exact criterion. Imported data may contain leading, trailing, or nonbreaking spaces that are hard to see.
Different spellings: “North-East,” “Northeast,” and “North East” are distinct strings. Check the source entries rather than relying on how a cell looks at a glance.
Blank-looking cells: A cell may be truly empty or may contain a formula that returns an empty string. These can behave differently in criteria checks, so verify the source formula when blank counts seem surprising.

COUNTIFS text matching is not case-sensitive: a criterion for “paid” also matches “Paid.” If capitalization is the only difference, it usually does not explain a wrong count. COUNTIFS also does not trim spaces or repair inconsistent data automatically. Cleaning the source or using a consistent helper column is often more reliable than making the counting formula increasingly complicated.

Use wildcards deliberately

For text criteria, an asterisk (*) matches any number of characters, while a question mark (?) matches one character. These are useful for partial matches, but they can make a criterion broader than expected. For example, “Ava*” matches any text beginning with Ava, including “Ava Chen,” “Ava Patel,” and “Avalon.”

With names in A2:A6—Ava Chen, Ava Patel, Maya Chen, Ava, and Ava Chen—the following counts names beginning with Ava. It returns 4. The criterion is a prefix match, not an exact match.

With those names in A2:A6, use =COUNTIFS(A2:A6,"Ava*"). The expected result is 4.

To search for a literal asterisk or question mark in text, place a tilde (~) before the wildcard character in the criterion. This escaping rule matters when the data itself contains symbols used as wildcards. If an exact match is intended, remove the wildcard and use the complete text as the criterion.

Handle dates and times as values

Excel stores real dates as serial numbers and may store a time as a fractional part of a day. Therefore, a cell that displays a date can also contain a time that is hidden by its number format. An exact criterion for a date at midnight may miss entries later on the same day.

A dependable way to count all real date-time values on one calendar day is to use a lower boundary at midnight on that day and an exclusive upper boundary at midnight the next day. The example below assumes D2 contains Jan 1, 2026 at 9 a.m., D3 contains Jan 1, 2026 at 5 p.m., D4 contains Jan 2, 2026 at midnight, and D5 contains the text “1/1/2026.” It counts the two real date-time entries on Jan 1, but not Jan 2 or the text entry.

With real Excel date-time values in D2:D5 as described, use =COUNTIFS(D2:D5,">="&DATE(2026,1,1),D2:D5,"<"&DATE(2026,1,2)). The expected result is 2.

DATE constructs a date value, and the less-than boundary includes every time before midnight on the following day. Text that merely looks like a date may not compare as a real date, so check imported data if some entries are omitted. Applying a date number format changes display, not the underlying value.

Understand AND and OR conditions

Separate criteria pairs in COUNTIFS are combined with AND. For example, a row must have both the chosen region and the chosen status. This is right for conditions such as “East and Paid,” but not for “Paid or Pending.”

For an OR condition across two distinct statuses, add separate COUNTIFS results. With statuses in B2:B6—Paid, Paid, Pending, Paid, Pending—the formula below returns 5, counting records that are Paid or Pending. Because those two labels cannot both be the same cell value, the groups do not overlap. If your alternatives can overlap, adding counts can count a record twice.

With statuses in B2:B6 as listed, use =COUNTIFS(B2:B6,"Paid")+COUNTIFS(B2:B6,"Pending"). The expected result is 5.

If the alternatives apply alongside another condition, repeat that condition in each COUNTIFS term. For example, count East-and-Paid plus East-and-Pending. For more complicated OR logic, write down which records should qualify first, then choose a formula structure that counts each qualifying record once. This prevents a common misconception: placing two criteria for the same range in one COUNTIFS formula does not normally mean “either one.”

A practical troubleshooting sequence

When a result looks wrong, inspect the formula and source data in a consistent order instead of changing several things at once:

Confirm the intended logic. Decide whether each condition must be true (AND) or whether alternatives should be added (OR).
Check each range’s start and end rows. Confirm that all criteria ranges have identical dimensions and refer to the same records.
Inspect the criteria text. Verify quotation marks, comparison operators, cell-reference concatenation, spelling, and any wildcard characters.
Look at the underlying values. Check for text-formatted numbers, date-time values, extra spaces, and formulas returning empty strings.
Test one condition at a time. Temporarily use a simpler COUNTIF or COUNTIFS calculation to identify which condition eliminates the records you expected to count.
Compare against a small sample. Find a few rows that should qualify and verify every criterion for each row, not just the final displayed result.

If a formula gives a count but not the count you expect, that usually indicates a logic or data mismatch rather than a broken function. If it gives a syntax error, review separators and quotation marks. If it gives #VALUE!, compare range dimensions. A result of zero is not necessarily an error: it may mean no row meets every supplied condition.

Frequently Asked Questions

Why does COUNTIFS return zero when matching cells are visible?
Visible text may contain trailing spaces, different punctuation, or a wildcard where you intended an exact match. Numbers and dates can also be stored as text. Inspect the underlying entries and test each criterion separately.

Can COUNTIFS count cells that contain part of a word?
Yes. Use * for any number of characters or ? for one character in a text criterion. For example, “*paid*” matches text containing paid anywhere. Remember that the match is not case-sensitive and may include more entries than an exact criterion.

Does COUNTIFS ignore filtered-out rows?
No. COUNTIFS checks the cells in its referenced ranges, including rows hidden by a filter. If the requirement is to count visible rows only, COUNTIFS alone does not provide that behavior; the formula needs a different approach.

Why does COUNTIFS show #VALUE!?
A frequent cause is that the criteria ranges have different dimensions. Make each range cover the same number of rows and columns, and check that the formula’s range and criteria arguments are paired correctly.

Is COUNTIFS case-sensitive?
No. COUNTIFS does not distinguish uppercase from lowercase in text criteria. If capitalization must determine whether a text value matches, COUNTIFS by itself is not sufficient for that requirement.

Why does a date criterion miss some records from the same day?
The cells may include times that are not visible in the current format, or some entries may be text rather than real dates. Use a start-of-day boundary and an exclusive next-day boundary for date-time values, and confirm that the source entries are real Excel dates.

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.