COUNTIFS Not Working With Multiple Criteria? Fix It

If COUNTIFS is not working with multiple criteria, check that every criteria range is the same size, that each criterion is paired with the right range, and that operators and text are written in the correct format. COUNTIFS applies all its criteria at once, so it counts rows that meet every condition—not rows that meet any one of them.

How COUNTIFS handles multiple criteria

COUNTIFS is designed to count records that satisfy several tests. Its arguments are supplied in range-and-criterion pairs: first a range to inspect, then the condition to apply to it, followed by another range and condition as needed. For example, you can count rows where the region is North and the payment status is Paid. The function is available in current Microsoft 365 Excel for Windows, Mac, and the web, as well as older Excel versions that support COUNTIFS.

With region names in A2:A6 and statuses in B2:B6, the formula is =COUNTIFS(A2:A6,"North",B2:B6,"Paid"). If those five rows contain North/Paid, North/Pending, South/Paid, North/Paid, and South/Paid, the result is 2. Both tests must be true on the same row. The order of the pairs can change, but the association between each range and its criterion must remain correct.

This is an important distinction from an OR test. Adding another pair to COUNTIFS does not give it another acceptable option; it narrows the count by adding another requirement. If the goal is “North or Paid,” the logic needs to be designed differently from “North and Paid.”

Check the argument pairs and range sizes

A quick first check is to count the arguments. COUNTIFS requires an even number of arguments after the function name because each criteria range needs a matching criterion. A missing criterion, an extra comma, or a criterion placed where a range belongs can cause an error or an unexpected count.

All criteria ranges must also have the same number of rows and columns. For example, A2:A100 can be paired with B2:B100, but not with B2:B99. Using whole-column ranges consistently, such as A:A and B:B, is also possible, though very large workbooks may calculate more slowly. For a reliable, efficient formula, use matching ranges that cover the data and avoid including a header in only one of them.

Confirm that every range-and-criterion pair is in the intended order.
Compare the starting and ending cells for every criteria range.
Check that ranges do not include different header or total rows.
Make sure the criterion is testing the field you intend, not a neighboring column.

Forgetting that each pair is evaluated row by row is another source of confusion. COUNTIFS does not count a matching region from one row and a matching status from a different row. The conditions have to line up on a single record.

Write criteria with operators correctly

Text criteria such as "North" can be written directly. Conditions using comparison operators need special attention: put the operator and value together inside the criterion text, such as ">100" or "<>Closed". If the value comes from a cell, join the operator to the cell reference with the ampersand operator.

With sales values in C2:C6 and the threshold 100 in E2, =COUNTIFS(C2:C6,">"&E2) counts sales greater than the value in E2. Using the sample values 120, 80, 150, 200, and 90, and with E2 set to 100, the result is 3. The greater-than sign is part of the criterion; writing it outside the quotation marks or failing to join it to E2 often causes the test to behave differently than expected.

Use >= when the boundary value should be included and <> when it should be excluded. Be precise about “greater than” versus “greater than or equal to”: a record exactly at the threshold is excluded by > and included by >=. Also note that numbers stored as text may not behave like numeric values in comparisons. If a count seems off, inspect the source cells and convert text-formatted numbers into real numbers where appropriate.

Use dates and text criteria carefully

Dates are a common reason a multiple-criteria formula returns zero. Excel stores real dates as serial numbers, even when the cells display a friendly format such as Jan 5. A date typed or imported as text may look identical on screen but will not necessarily compare as a date. Check a suspicious cell’s format and value, and, where needed, convert imported text dates before counting.

Suppose dates are in A2:A6, with actual date values Jan 5, Jan 31, Feb 1, Jan 20, and Feb 5, 2026. To count January dates, use =COUNTIFS(A2:A6,">="&DATE(2026,1,1),A2:A6,"<"&DATE(2026,2,1)). It returns 3 for this sample. The less-than condition for Feb 1 includes all times on Jan 31 if the cells contain timestamps, while avoiding the risk of excluding a late-day value. The DATE function also avoids ambiguity between regional date formats.

Text criteria can fail because of extra spaces, nonbreaking spaces from copied web data, or subtle spelling differences. COUNTIFS is generally not case-sensitive, so it does not distinguish “North” from “NORTH”; it does distinguish different text such as “North ” with a trailing space. Clean the source data if it contains inconsistent spacing, rather than adding more criteria to compensate for messy values.

Handle OR conditions and wildcards

Multiple pairs in one COUNTIFS formula mean AND. For an OR condition involving distinct options, add separate COUNTIFS results. With regions in A2:A6 and statuses in B2:B6, =COUNTIFS(A2:A6,"North",B2:B6,"Paid")+COUNTIFS(A2:A6,"South",B2:B6,"Paid") counts Paid rows from either North or South. For the sample rows described earlier, the result is 3.

Be cautious when adding results: if a row could match both alternatives, it may be counted twice. For example, summing one count for a broad text pattern and another for a narrower pattern can overlap. Choose alternatives that cannot overlap, or use a different approach that counts each record only once. COUNTIFS itself has no single criterion syntax that means “this value or that value” for two separate text choices in the same field.

Wildcards are useful when the text is only partly known. An asterisk matches any number of characters, and a question mark matches one character. With product names in A2:A6 containing Miller, Mills, Smith, Miller-Smith, and a blank, =COUNTIFS(A2:A6,"*Miller*") returns 2 because two names contain Miller. A tilde can escape a wildcard when you need to search for a literal asterisk or question mark. Remember that wildcards apply to text matching, not numeric comparisons.

Common causes of zero or incorrect counts

A zero result does not automatically mean COUNTIFS is broken. Often, the combined conditions simply do not occur together in the data. Test each criterion separately to see whether it finds any records, then add the next range and criterion. This makes it easier to spot which condition eliminates the matches.

Wrong logic: You intended OR, but added another pair and created an AND test.
Invisible characters: Imported text has leading spaces, trailing spaces, or nonbreaking spaces.
Wrong data type: Numbers or dates are stored as text, so comparisons do not match as expected.
Misplaced operator: A comparison operator is not joined correctly to a referenced threshold.
Misaligned ranges: One range starts or ends on a different row, so conditions are checked against the wrong records.
Unexpected blanks: A criterion such as "<>" can count nonblank cells, but cells with formulas returning an empty string can make blank-related checks surprising.

To diagnose a result, compare it with a filtered view of the same data. Apply the intended conditions using the column filters and count the visible matching records. If the filtered rows and COUNTIFS disagree, inspect the exact cell contents and the formula references. In Excel for Microsoft 365 on Windows, select the formula cell and review the formula in the formula bar; use Formulas > Evaluate Formula to step through how Excel evaluates it. On Mac, the Evaluate Formula command is also available from the Formulas tab, although ribbon layout can vary by window size. Excel for the web has a more limited formula-evaluation workflow, so checking ranges and testing criteria in separate cells can be simpler there.

When to use another approach

COUNTIFS is the clearest choice when you need a count of rows meeting straightforward AND conditions. For OR logic, adding separate COUNTIFS results is often easiest to read when the alternatives are distinct. If conditions become numerous, overlap, or depend on more complicated row-by-row logic, consider a helper column that labels qualifying records, then count that column. A helper column also makes the rule visible to people reviewing the workbook.

For flexible summaries across categories, a PivotTable may be easier to maintain than a long formula. Select the data, choose Insert > PivotTable, and arrange fields in the Rows and Filters areas; use the Values area to summarize a field. A PivotTable is useful for exploring counts by several dimensions, while COUNTIFS is better when a fixed result needs to appear in a report cell and update as criteria change. Neither method repairs inconsistent source data, so cleanup remains important.

In Microsoft 365, formulas using FILTER or other dynamic-array functions can help inspect matching records, but they are not required to make COUNTIFS work. The simpler troubleshooting path is usually to verify the data, test one condition at a time, and then rebuild the paired criteria ranges carefully.

Frequently Asked Questions

Does COUNTIFS use AND or OR between criteria?
It uses AND across criteria pairs: every pair must be true for a row to count. For OR conditions, add separate COUNTIFS formulas when the alternatives do not overlap, and be alert to double-counting.

Why does COUNTIFS return zero when the values look identical?
The cells may contain extra spaces, nonprinting characters, different data types, or a slightly different spelling. Excel’s displayed formatting can hide those differences. Inspect the source values and test each criterion separately.

Can COUNTIFS use a cell reference as a criterion?
Yes. A direct text or numeric reference can be used as a criterion. For a comparison such as greater than a value in E2, join the operator and reference inside the criterion expression, for example ">"&E2.

Why does COUNTIFS give the wrong result for dates?
Some entries may be text rather than real Excel dates, or the date range may exclude a boundary value. Use actual date values and consider a start-inclusive, next-period-exclusive test when cells may contain times.

Can COUNTIFS distinguish uppercase and lowercase text?
No. COUNTIFS text comparisons are not case-sensitive. If capitalization must determine whether a row counts, use a different formula design with a case-sensitive comparison or create a helper column.

Do all COUNTIFS ranges have to be the same size?
Yes. Each criteria range must have matching dimensions. Align the start and end rows for every range so each condition is evaluated against the same record.

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.