Excel Formulas to Count Rows Matching Multiple OR Criteria

Managing complex datasets often leads to frustration when standard formulas fail to aggregate OR-based criteria efficiently. Typically, analysts track standard funding sources-such as municipal allocations or private donations-using repetitive, bloated addition formulas.

Fortunately, mastering advanced array constants grants you the ability to streamline this process into a single, elegant cell. A key stipulation to remember is that this method assumes criteria are mutually exclusive to avoid double-counting. For instance, to count rows representing "Federal" or "State" green-energy grants, we combine SUM and COUNTIF.

Below, we will dissect this exact formula structure so you can apply it to your own reports.

Excel is an incredibly powerful tool for data analysis, but its default behavior can sometimes feel limiting. One of the most common challenges users face is performing conditional counts with "OR" logic. By default, functions like COUNTIFS are designed around "AND" logic-meaning they only count a row if all specified criteria are met.

But what if you need to count rows that match any of multiple criteria? For instance, how do you count orders where the region is "North" OR the product is "Software"? Or how do you count sales reps who sold either "Apples", "Oranges", or "Pears"?

In this comprehensive guide, we will explore several highly effective methods to count rows matching multiple criteria using "OR" logic, ranging from classic Excel formulas to modern dynamic array functions.

Method 1: The SUM and COUNTIF/COUNTIFS Combo (Single Column, Multiple Criteria)

If you need to count rows where a single column matches any of several criteria, the easiest and most elegant solution is combining the SUM and COUNTIF (or COUNTIFS) functions with an array constant.

The Formula Syntax

=SUM(COUNTIF(range, {"criterion1", "criterion2", "criterion3"}))

How It Works

Normally, COUNTIF takes a single value and returns a single count. However, by wrapping your criteria in curly braces {} and separating them with commas, you pass an "array constant" to the function. This forces Excel to evaluate the criteria independently, returning an array of counts. Finally, the SUM function adds those individual counts together.

Step-by-Step Example

Imagine you have a list of store locations in column A, and you want to count how many stores are in "New York", "Boston", or "Chicago".

To count the rows matching "New York", "Boston", or "Chicago", use this formula:

=SUM(COUNTIF(A1:A6, {"New York", "Boston", "Chicago"}))

Evaluation steps:

• COUNTIF(A1:A6, {"New York", "Boston", "Chicago"}) returns the array: {1, 2, 1} (1 New York, 2 Bostons, 1 Chicago).
• SUM({1, 2, 1}) adds these values up to return 4.

Note: If you have your criteria listed in a range of cells (e.g., D1:D3) instead of hardcoding them, use SUMPRODUCT instead of SUM: =SUMPRODUCT(COUNTIF(A1:A6, D1:D3)).

Method 2: SUMPRODUCT with Addition Logic (Multiple Columns, Multiple Criteria)

The COUNTIF array method works beautifully for a single column. But what if your criteria span across different columns? For example, you want to count rows where the Region is "East" OR the Product is "Tablet".

For multi-column "OR" logic, SUMPRODUCT is the ultimate tool. In Boolean algebra, addition (+) represents the "OR" operator, while multiplication (*) represents the "AND" operator.

The Formula Syntax

=SUMPRODUCT(--((range1="criterion1") + (range2="criterion2") > 0))

How It Works

• (range1="criterion1") and (range2="criterion2") produce arrays of TRUE and FALSE values.
• Adding these arrays together (+) acts as an OR gate. If a row matches one criterion, it yields 1 + 0 = 1. If it matches both, it yields 1 + 1 = 2. If it matches neither, it yields 0 + 0 = 0.
• The > 0 comparison ensures that rows matching both criteria are not double-counted (transforming 2 back into TRUE, which is equivalent to 1).
• The double unary operator (--) converts the resulting TRUE/FALSE values into 1s and 0s, which SUMPRODUCT then sums.

Step-by-Step Example

Consider the following dataset:

To count rows where Region is "East" OR Product is "Tablet":

=SUMPRODUCT(--(((A1:A5="East") + (B1:B5="Tablet")) > 0))

Evaluation breakdown:

• Row 1: East (TRUE) + Laptop (FALSE) = 1. Is 1 > 0? Yes (TRUE).
• Row 2: West (FALSE) + Tablet (TRUE) = 1. Is 1 > 0? Yes (TRUE).
• Row 3: East (TRUE) + Tablet (TRUE) = 2. Is 2 > 0? Yes (TRUE). (Notice how this prevents double-counting!)
• Row 4: South (FALSE) + Phone (FALSE) = 0. Is 0 > 0? No (FALSE).
• Row 5: North (FALSE) + Laptop (FALSE) = 0. Is 0 > 0? No (FALSE).

The resulting array of {TRUE, TRUE, TRUE, FALSE, FALSE} is converted by -- to {1, 1, 1, 0, 0}, which sums to 3.

Method 3: Adding Multiple COUNTIFS (For Mutually Exclusive Criteria)

If your "OR" criteria are completely mutually exclusive-meaning a single row can never meet more than one criterion at the same time-you can simply add multiple COUNTIFS formulas together.

The Formula Syntax

=COUNTIFS(range, "criterion1") + COUNTIFS(range, "criterion2")

When to Use This

This method is highly readable and perfect for beginners. For example, if you want to count how many employees are in either the "HR" department or the "IT" department, an employee can only belong to one department. There is zero risk of double-counting.

Example:

=COUNTIFS(C2:C100, "HR") + COUNTIFS(C2:C100, "IT")

Warning: Do not use this method across different columns where overlapping data can occur. For instance, =COUNTIFS(A2:A100, "East") + COUNTIFS(B2:B100, "Tablet") will double-count any row that is both "East" and "Tablet". Use the SUMPRODUCT method instead for such scenarios.

Method 4: FILTER and ROWS (The Modern Excel 365 Way)

If you are using modern versions of Excel (Excel 365 or Excel 2021+), you have access to dynamic array functions. Combining FILTER and ROWS provides an incredibly intuitive way to perform conditional counts with "OR" logic.

The Formula Syntax

=ROWS(FILTER(range, (criteria1) + (criteria2)))

How It Works

The FILTER function isolates only the rows that match your criteria. Just like in the SUMPRODUCT method, the + symbol acts as the "OR" operator. Once the filtered list of rows is returned, the ROWS function simply counts how many rows are in that filtered array.

Step-by-Step Example

Using the same dataset from Method 2, if you want to count rows where Region is "East" OR Product is "Tablet":

=ROWS(FILTER(A1:B5, (A1:A5="East") + (B1:B5="Tablet")))

Why this is great: It is visually straightforward and matches how we think about filtering data manually. If no rows match the criteria, FILTER will return a #CALC! error. To prevent this, wrap the formula in IFERROR:

=IFERROR(ROWS(FILTER(A1:B5, (A1:A5="East") + (B1:B5="Tablet"))), 0)

Summary: Which Method Should You Use?

To help you choose the best formula for your spreadsheet, consult the quick-reference table below:

By mastering these four techniques, you can tackle any complex counting requirement in Excel without relying on messy helper columns or VBA code. Try them out in your next spreadsheet and enjoy cleaner, more efficient data analysis!