Excel COUNTIFS: How to Count Rows With OR Conditions

Formulas & functions · Updated November 19, 2026

COUNTIFS uses AND logic between its criteria pairs: a row must meet every condition you give it. To count rows that meet either of two alternatives, add separate COUNTIFS results together, or—in Microsoft 365—use an array of alternatives inside COUNTIFS and wrap it in SUM.

What COUNTIFS Does

COUNTIFS counts records that satisfy multiple conditions. Each condition is supplied as a range-and-criterion pair. For example, you can count orders from the East region that also have at least 10 units. The function checks each row across all the listed ranges and counts the row only when every criterion is true.

Assume regions are in A2:A100 and unit totals are in C2:C100. This formula counts rows where the region is East and the units are 10 or more:

=COUNTIFS(A2:A100,"East",C2:C100,">=10")

The comma separates criteria pairs; it does not mean “or.” This is the main distinction to remember: multiple COUNTIFS pairs combine as AND, while OR usually requires adding counts for separate alternatives. The ranges should also cover corresponding rows and have the same dimensions. If one range starts at row 2 and another at row 3, Excel may return an error or count unintended records.

Count Either Value in One Column

For a basic OR test, such as counting records whose status is either Complete or Pending, add the two individual counts. With statuses in D2:D100, use:

=COUNTIF(D2:D100,"Complete")+COUNTIF(D2:D100,"Pending")

COUNTIF is sufficient here because each alternative uses one range. If you want to keep the function name COUNTIFS, the equivalent is to add two COUNTIFS calculations. With statuses in D2:D100, this counts Complete or Pending entries:

=COUNTIFS(D2:D100,"Complete")+COUNTIFS(D2:D100,"Pending")

Each part produces a number, and the plus sign totals those numbers. This works well when the alternatives are clearly distinct values in one column. It is also easy to extend: add another COUNTIFS term for another status. The drawback is that a long list of alternatives makes the formula longer and more demanding to maintain.

In Microsoft 365, an array of criteria can be a compact option. With statuses in D2:D100, this formula counts Complete and Pending entries and sums the two counts:

=SUM(COUNTIF(D2:D100,{"Complete","Pending"}))

For COUNTIFS with an additional condition, assume statuses are in D2:D100 and departments are in B2:B100. This counts Complete or Pending records in the Sales department:

=SUM(COUNTIFS(D2:D100,{"Complete","Pending"},B2:B100,"Sales"))

The department criterion applies to each status alternative. If a workbook must also work in older Excel releases, separate COUNTIFS terms added together are often the clearest compatibility-minded choice.

Combine OR With Other Conditions

A common request is to count rows that satisfy an OR condition and an additional requirement—for instance, East or West region, provided units are at least 10. Treat each region as a separate COUNTIFS calculation and add the results. With regions in A2:A100 and units in C2:C100, use:

=COUNTIFS(A2:A100,"East",C2:C100,">=10")+COUNTIFS(A2:A100,"West",C2:C100,">=10")

Suppose the six data rows are East/12 units, West/8, North/15, East/9, West/14, and South/11. The East part counts the first row, and the West part counts the fifth; the formula returns 2. It counts qualifying rows, not the sum of the units. To find total units instead, a different function or formula is needed.

This approach is often called “OR within AND”: the region can be East or West, but the unit threshold must also be met. More generally, write one COUNTIFS for each OR branch and put the shared AND conditions in every branch. For example, if the same date limit applies to both regions, include that date criterion in both terms. Leaving it out of one term changes the logic.

For several alternatives in the same field, an array criterion can be shorter in Microsoft 365. With region values in A2:A100 and units in C2:C100, this counts East or West rows with at least 10 units:

=SUM(COUNTIFS(A2:A100,{"East","West"},C2:C100,">=10"))

The SUM is important: COUNTIFS evaluates the alternatives and produces counts, while SUM combines them into one total. For clarity or compatibility, the two-term version above expresses the same logic explicitly.

When Adding Counts Can Double-Count

Adding separate counts is safe when one row cannot satisfy more than one branch. A region cell cannot normally be both East and West, so the region example has no overlap. But alternatives can overlap when they refer to different columns. For example, one record might have status Open and priority High. Adding “Open” rows to “High priority” rows would count that record twice.

To count rows that are Open or High priority exactly once, use the inclusion-exclusion method: add both groups, then subtract the rows that belong to both. Assume statuses are in D2:D100 and priorities are in E2:E100:

=COUNTIFS(D2:D100,"Open")+COUNTIFS(E2:E100,"High")-COUNTIFS(D2:D100,"Open",E2:E100,"High")

The final COUNTIFS identifies the overlap and subtracts it once. This distinction matters whenever the OR branches are not mutually exclusive. Before adding counts, ask whether a single row could meet more than one branch. If yes, either remove the overlap with a formula like this or use a method that counts each row once.

Criteria for Text, Numbers, Dates, and Wildcards

COUNTIFS criteria can test exact text, numbers, comparisons, and patterns. Put comparison operators such as greater than or equal to inside quotation marks. With quantities in C2:C100, this counts values of 10 or more:

=COUNTIFS(C2:C100,">=10")

You can also join an operator to a cell reference. Assume dates are in F2:F100 and the start date is in H2. This counts dates on or after the date in H2:

=COUNTIFS(F2:F100,">="&H2)

In HTML, the ampersand appears as &; in Excel, the formula uses the normal & operator to join the comparison text to the cell value. Using a cell reference makes it easier to change the threshold without editing the formula. For date periods, two criteria on the same date range can set a start and end boundary; both conditions must be true for a date to count.

Text criteria can use wildcards. An asterisk matches any number of characters, while a question mark matches one character. For instance, with product names in A2:A100, this counts names that begin with “Pro”:

=COUNTIFS(A2:A100,"Pro*")

To search for an actual asterisk or question mark rather than use it as a wildcard, precede it with a tilde. These are useful tools, but they do not change the AND/OR rule: separate criteria pairs still have to be satisfied together.

Common COUNTIFS OR Problems

  • Putting alternatives in separate pairs: COUNTIFS with criteria for East and West in the same region range asks for a row that is both East and West. Use separate counts added together or an array criterion instead.
  • Using mismatched ranges: Criteria ranges should be the same size and aligned to the same records. Whole-column references can be convenient, but mixing a whole column with a short range is a frequent source of errors.
  • Counting a row twice: Separate OR branches can overlap. Check for overlap, especially when conditions refer to different columns.
  • Unexpected text matches: Leading or trailing spaces, inconsistent spelling, and numbers stored as text can prevent a record from matching the intended criterion. Inspect representative source cells before changing the formula.
  • Confusing a count with a sum: COUNTIFS returns how many rows match. It does not add the values in a units or amount column. Use a summing function when the desired result is a total of values.

When a result looks wrong, test each OR branch separately in temporary cells. If the individual counts are correct but the combined total is not, the cause is likely overlap or the way the branches were combined. If one branch is already wrong, check its range alignment, exact text, and comparison criteria first.

Choosing a Formula and Checking Compatibility

For two or three alternatives, adding separate COUNTIFS formulas is usually easiest to read and troubleshoot. It works across many Excel versions and makes each branch visible. For several alternatives in Microsoft 365, an array constant inside COUNTIFS can reduce repetition, provided the formula is wrapped in SUM so the alternative counts are combined.

For more complex logic—especially multiple overlapping OR conditions—a helper column can be clearer than a very long formula. A helper column can label each row as qualifying or not, after which a simple count can report qualifying records. This takes extra worksheet space but is often easier for another person to audit. A PivotTable can also summarize categories, though it is less suited to arbitrary row-level logic involving custom OR conditions.

The core COUNTIFS behavior is the same in Excel for Microsoft 365 on Windows, Mac, and the web. The examples use commas as argument separators, as requested for standard English-language formula syntax; some regional settings display semicolons instead. If an array-criteria formula is not accepted in an older Excel version, use the explicit sum of separate COUNTIFS terms.

Frequently Asked Questions

Can COUNTIFS use OR?
Yes, but separate criteria pairs in COUNTIFS mean AND, not OR. For OR logic, add individual COUNTIFS results, or use an array of alternatives and SUM in supported Excel versions.

How do I count cells that contain either of two words?
Use one COUNTIF for each word and add the results. If you use wildcard patterns, remember that a cell could match both patterns; adding the counts may then count that cell twice.

Can COUNTIFS do OR in one column and AND in another?
Yes. Create one COUNTIFS term for each alternative in the first column, repeat the shared condition for the second column in every term, and add the results. This gives OR between branches and AND within each branch.

Why does COUNTIFS return zero when I expect a match?
Check for extra spaces, inconsistent values, numbers stored as text, dates that are actually text, and ranges that do not line up. Also verify that comparison operators are included inside quoted criteria strings.

Does COUNTIFS count a matching row once if two OR conditions are true?
Not necessarily. If you add separate counts, an overlapping row appears in each matching count. Subtract the intersection, or choose a row-by-row method that counts each qualifying record once.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.