Count Rows With OR Logic in Excel and Google Sheets
To count rows that meet at least one of several conditions, use OR logic: a row qualifies when any condition is true. In Excel or Google Sheets, COUNTIF can count each condition and combine the results, but take care not to count a row twice when it matches more than one condition.
What OR means when counting rows
In a row-counting task, OR means “this condition or that condition, or both.” Suppose a table has one record per row and a Status column. If you want the number of rows marked “Open” or “Pending,” a row counts if its status is either value. It does not need to meet both conditions.
This differs from AND logic, where a row must satisfy every condition. For example, “Status is Open AND Region is West” counts only rows meeting both tests. “Status is Open OR Region is West” counts rows meeting either test, including rows that meet both.
Before choosing a formula, identify what you mean by “count rows.” Usually, you want to count each qualifying record once, even if it matches multiple conditions. That detail determines whether simple addition is appropriate.
Count rows matching either value in one column
For two distinct text values in one column, add two COUNTIF results. If statuses are in cells B2:B100, this counts rows marked Open or Pending:
=COUNTIF(B2:B100,"Open")+COUNTIF(B2:B100,"Pending")
COUNTIF counts cells matching one criterion. Because a cell in a single column cannot be exactly “Open” and exactly “Pending” at the same time, the two groups do not overlap, so adding their counts gives the number of qualifying rows.
For several alternatives, you can add more COUNTIF results. In current Excel and Google Sheets, this compact pattern also works for a list of exact alternatives:
=SUM(COUNTIF(B2:B100,{"Open","Pending","Review"}))
In some spreadsheet locales, formulas use semicolons instead of commas, and array-constant separators can vary. If the formula is rejected, try the separate COUNTIF terms or check the spreadsheet’s regional formula syntax.
Count rows when conditions are in different columns
If each condition applies to a different column, adding COUNTIF counts can double-count rows that satisfy both. For example, suppose A2:A100 contains departments and B2:B100 contains priority labels. You want rows where Department is Sales or Priority is High:
=SUMPRODUCT(--(((A2:A100="Sales")+(B2:B100="High"))>0))
The comparisons produce TRUE or FALSE values for each row. Adding the results gives 0 if neither condition is true, 1 if one is true, and 2 if both are true. The test greater than zero turns either 1 or 2 into a single qualifying result, and SUMPRODUCT adds those row results. Each row is therefore counted once.
For a row that must meet one condition from each of two groups—for example, Department is Sales or Support, and Status is Open or Pending—use OR within each group and AND between groups:
=SUMPRODUCT(--(((A2:A100="Sales")+(A2:A100="Support"))>0),--(((B2:B100="Open")+(B2:B100="Pending"))>0))
Here both SUMPRODUCT arrays must be nonzero for a row to contribute to the total. This distinction is useful: OR joins alternatives, while AND combines requirements.
Use COUNTIFS for AND, not as a direct OR operator
COUNTIFS counts rows or records meeting multiple criteria together. Its criteria are combined with AND logic: a row must meet the criterion for every specified range. For example, =COUNTIFS(A2:A100,"Sales",B2:B100,"Open") counts rows that are both in Sales and Open.
To get OR logic between different values for the same field, you can add separate COUNTIFS formulas when the groups cannot overlap:
=COUNTIFS(A2:A100,"Sales",B2:B100,"Open")+COUNTIFS(A2:A100,"Support",B2:B100,"Open")
This counts Sales-and-Open rows plus Support-and-Open rows. Since a row has only one value in the Department field, those groups are mutually exclusive. If your conditions can overlap, use a row-by-row test that counts qualifying rows once rather than adding group totals blindly.
Choose the formula and avoid common mistakes
- Alternatives in one column: Add COUNTIF results for exact values, or use SUM with an array of criteria where supported.
- OR across columns, count each row once: Use SUMPRODUCT with a greater-than-zero test.
- Several AND requirements: Use COUNTIFS, or combine row-level tests carefully when the logic includes both AND and OR.
- Criteria stored in cells: Refer to those cells rather than typing values into the formula. For example,
=COUNTIF(B2:B100,D1)+COUNTIF(B2:B100,D2).
Keep the ranges aligned and the same size. In a SUMPRODUCT formula, comparing A2:A100 with B2:B99 can produce an error or an unintended result. Avoid including headers unless you intend to test them. Also consider whether blank cells should qualify; a criterion such as "<>" means not blank in common spreadsheet criteria syntax, while a plain comparison to a value may behave differently with blanks.
Text matching in COUNTIF is generally not case-sensitive, so “Open” and “open” are treated alike. Wildcards can also affect criteria: an asterisk matches a sequence of characters, and a question mark matches one character. If you need literal wildcard characters, escape them with a tilde. For exact matching with case sensitivity, a different formula approach is needed.
Frequently Asked Questions
How do I count rows that meet either of two conditions in Excel?
For two alternatives in one column, add two COUNTIF formulas. For conditions in different columns where each row should count only once, use a SUMPRODUCT formula with the OR tests added and compared with zero.
Does COUNTIFS use OR or AND logic?
COUNTIFS combines its criteria with AND logic: all specified conditions must be met. For OR alternatives, add separate COUNTIFS results when the groups cannot overlap, or build a row-level OR test when they can.
How do I stop OR formulas from counting a row twice?
Do not simply add totals for conditions that can both be true for the same row. Test each row’s combined conditions as greater than zero, then sum those yes-or-no results, as in the SUMPRODUCT example.
Can I count rows that match any value in a list?
Yes. In Excel or Google Sheets, use a SUM of COUNTIF results against the list of alternatives, or write one COUNTIF per value. For conditions across columns, use a row-level test so overlaps are counted once.
Why is my formula returning zero?
Check that the criterion text matches the data, the ranges cover the intended rows, and numbers stored as text are not being compared with numeric values. Also check for leading or trailing spaces and your spreadsheet’s comma or semicolon separators.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.