COUNTIFS Not Counting Correctly? Fix the Criteria

If COUNTIFS is returning zero or the wrong total in Excel, first check that every criteria range has the same size and shape, then verify that the criteria match the underlying values exactly. Hidden spaces, dates stored as text, incorrect operator syntax, and wildcard behavior are common causes; the checks below help isolate which one is affecting your result.

Start with the quickest checks

Before rebuilding a formula, inspect the ranges and the values it is meant to count. A formula can be syntactically valid while still counting fewer rows than expected because one criterion is not matching the data.

Confirm that each criteria range covers the same rows and columns.
Check whether the criteria cell contains the value you expect, without an extra space or an unexpected character.
Determine whether dates and numbers are real numeric values or text that only looks numeric.
For conditions such as greater than or equal to a cell value, join the operator and cell reference with &.
Check whether multiple conditions should all be true, or whether you actually need an either-or count.
If the displayed result is stale, check whether Excel is calculating formulas automatically.

Test each criterion on its own when possible. If the individual counts are already unexpected, the issue is likely the values or the criterion itself. If they look right separately but the combined COUNTIFS result is too small, inspect how the conditions overlap: COUNTIFS counts rows that satisfy every supplied condition at once.

How COUNTIFS evaluates conditions

COUNTIFS counts records that meet multiple conditions. Its arguments are supplied in pairs: a criteria range followed by the condition to apply to that range. For example, with regions in A2:A6 and order statuses in B2:B6, the formula below counts orders that are both from the East and Open:

With regions in A2:A6 and statuses in B2:B6, where the five rows are East/Open, East/Closed, West/Open, East/Open, and West/Closed, use =COUNTIFS(A2:A6,"East",B2:B6,"Open"). The result is 2 because two rows meet both conditions.

Each additional pair adds another condition that must be true for the same row. This is an AND relationship, not a request to count a match in either range. COUNTIFS also does not distinguish uppercase from lowercase in ordinary text criteria, so a criterion of East normally matches EAST as well. It does distinguish different characters, spaces, and punctuation.

COUNTIFS is not the same as adding separate counts for separate conditions. If the desired result is “East or West,” you may need to add two COUNTIFS results, taking care not to double-count rows if the categories can overlap. That distinction explains many totals that seem unexpectedly low.

Check the dimensions of every range

All criteria ranges in a COUNTIFS formula must have the same dimensions and corresponding row positions. A range that starts on row 2 cannot sensibly be paired with one that starts on row 3: the conditions would no longer refer to the same records. Excel may return an error when range dimensions conflict, but equal-sized ranges can still be misaligned and silently count the wrong rows.

For example, if dates are in D2:D100, regions in A2:A100, and statuses in B2:B100, use those same row boundaries in each range. Avoid mixing A2:A100 with B2:B99 or including a header in only one of the ranges. When adding new records, check that the formula’s ranges expand to include them. Excel Tables can help because structured references typically adjust as rows are added.

For a practical check, click each range reference in the formula bar or select the range directly in the formula. Verify the first and last row, and confirm that the columns contain the fields described by their criteria. A range can be the right size but the wrong column, such as testing a status criterion against a column of dates.

Review criteria syntax and operators

Text criteria can be written directly in quotation marks, such as "Open". Comparison criteria use operators including ">", ">=", "<", "<=", and "<>". When the comparison value is in a cell, combine the operator with the reference using the concatenation symbol &.

With scores in C2:C6 and the threshold in E2, use =COUNTIFS(C2:C6,">="&E2) to count scores at least as large as E2. If the scores are 72, 84, 90, 65, and 84, and E2 contains 84, the result is 3. Writing ">=E2" instead asks Excel to match the literal text criteria that begins with the characters “>=E2”; it does not read E2 as a cell reference.

Check the exact text inside criteria cells, too. If a criterion is typed as Open but the source contains Open – Pending, an exact text criterion will not match that longer label. Use a wildcard only when a partial match is truly intended, rather than adding one as a guess.

Dates and numbers that look right but do not match

Excel stores genuine dates as serial numbers and displays them using a date format. Text that looks like a date is not necessarily the same value, so a date condition may miss cells imported from a report or copied from a website. Select a suspect cell and check the formula bar and number format; changing the format alone does not convert text into a real date.

For dates in D2:D5, where the values are January 1, January 15, January 31, and February 1, 2026, use the following formula to count January dates:

With dates in D2:D5, use =COUNTIFS(D2:D5,">="&DATE(2026,1,1),D2:D5,"<"&DATE(2026,2,1)). The result is 3. The inclusive lower limit and exclusive first day of the next month avoid relying on the last day of the month and also work when date-time values include a time.

The same text-versus-number issue affects quantities, IDs, and amounts. A cell containing the text “100” may not behave like the numeric value 100 in a comparison. Imported data can also contain apostrophes or spaces that are not obvious on the worksheet. Convert or clean source values where needed, and test a few cells rather than assuming that their appearance proves their stored type.

Find hidden spaces and wildcard effects

Text that appears identical can differ because one entry contains a leading or trailing space, a nonbreaking space, or another invisible character. Such characters often arrive with copied or imported data. Comparing the cell with an expected value in the formula bar may reveal an extra space; using a helper column to clean the source can make later counts more reliable. TRIM removes ordinary extra spaces, but it does not remove every nonprinting or nonbreaking character.

COUNTIFS supports two wildcard characters in text criteria: an asterisk * matches any number of characters, and a question mark ? matches one character. For product names in A2:A6, where the entries are Widget, Widget Pro, Gadget, Widget, and Widget-Pro, use =COUNTIFS(A2:A6,"Widget*") to count entries beginning with Widget. The result is 4. This is a prefix match; it does not require the whole cell to equal Widget.

To search for an actual asterisk or question mark rather than use it as a wildcard, precede it with a tilde. For example, the criterion "~*" looks for a literal asterisk. This matters when product codes or imported labels include wildcard characters. Also remember that a wildcard can make a criterion broader than intended: "*East*" may match any cell containing East, not just the category East.

Separate AND conditions from OR conditions

Every pair in one COUNTIFS formula is combined with AND. Suppose a report asks for all orders that are both East and Open; one COUNTIFS formula is appropriate. If it asks for orders that are East or West, those alternatives need separate treatment. For mutually exclusive region labels, adding two counts is straightforward.

With regions in A2:A6, where the entries are East, West, North, East, and West, use =COUNTIFS(A2:A6,"East")+COUNTIFS(A2:A6,"West"). The result is 4. If the alternatives can overlap—for example, two text conditions that both match the same cell—adding the counts can count a row twice. Define the intended categories carefully before choosing a formula.

Likewise, two conditions on the same range in one COUNTIFS formula usually narrow the result rather than create alternatives. A lower and upper bound can be useful for a range of values, but “greater than 10 or less than 3” is not one continuous interval. Break the alternatives into separate counts or use another approach that matches the logic you need.

Check recalculation and the formula itself

If the source data changed but the result did not, check Excel’s calculation mode. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and select Automatic. A workbook using manual calculation may not refresh formulas until recalculation. Press F9 to calculate formulas in open workbooks; on some Mac keyboards, you may need Fn+F9. The exact key behavior depends on the keyboard settings.

Also look for a missing or extra criterion pair, a misplaced comma, or quotation marks that do not close. Excel formulas in some regional settings use a list separator other than a comma; if a formula with correct logic is rejected, check the separator expected by your Excel installation. The examples here use commas, as in a standard English-language Excel setup.

A useful diagnostic is to build the formula in stages. Start with one pair, confirm its result, then add the next pair and compare. If the count drops to zero after adding a condition, investigate that field’s values and the criterion syntax. This is often faster than changing several parts of a long formula at the same time.

Frequently Asked Questions

Why does COUNTIFS return zero when I can see matching cells?
Check for extra spaces, text-versus-number differences, dates stored as text, and criteria that require an exact rather than partial match. Also confirm that the criteria range covers the rows containing the visible matches.

Does COUNTIFS distinguish uppercase and lowercase?
No. COUNTIFS text comparisons are generally case-insensitive, so East and EAST match. If capitalization must determine the result, COUNTIFS alone is not the right case-sensitive test.

Can COUNTIFS count cells that contain a word?
Yes. Use asterisks around the word for a contains match, such as "*East*". This may also match longer labels containing those letters, so use an exact criterion when only the standalone category should count.

Why does my COUNTIFS date criterion miss some dates?
The cells may contain text rather than real Excel dates, or may include time values that make an upper-bound comparison exclude records later on the final day. A lower bound and an exclusive upper bound at the start of the next period are usually a dependable way to count a date interval.

Can COUNTIFS use OR logic?
One formula combines its criteria with AND. For simple, separate alternatives, add separate COUNTIFS results. Check whether the alternatives overlap so the same row is not counted more than once.

Why does a formula work for old rows but miss new ones?
The ranges may stop before the new data. Extend the criteria ranges consistently or use an Excel Table so references can expand as records are added.

Make the result dependable

When COUNTIFS is wrong, the fastest route is to verify the data type and criteria first, then check range alignment and the logic connecting conditions. Test the formula one pair at a time, use date boundaries that match Excel’s stored values, and choose wildcards deliberately. Once the underlying data is consistent and every range covers the intended records, COUNTIFS is a reliable way to count rows that satisfy several conditions together.

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.