Count Rows with Multiple OR Criteria in Excel

This article explains how to count rows in Excel that meet any one of several OR criteria—for example, counting orders that are either “Pending” or “Shipped,” or rows where the product is “A” or the region is “West.” You will learn the correct formulas (SUMPRODUCT, COUNTIFS with array constants, and others), see worked examples, and avoid common pitfalls.

Formula

The most flexible way to count rows with multiple OR criteria is the SUMPRODUCT formula:

=SUMPRODUCT( ((range1 = "crit1") + (range2 = "crit2") + ...) * 1 )

Each condition like (A2:A100 = "Pending") returns an array of TRUE/FALSE. Adding them with + works like OR: if any condition is TRUE, the sum for that row is at least 1. Multiplying by 1 converts TRUE/FALSE to 1/0 so SUMPRODUCT can sum them. The result is the number of rows where at least one condition is true.

If you need to count rows where all conditions must be true (AND), you would multiply the arrays instead: (cond1) * (cond2).

Worked Example

Suppose you have a list of orders in column A (status) and column B (region). You want to count rows where the status is “Pending” OR the region is “West.”

Enter this formula:

=SUMPRODUCT( ((A2:A100 = "Pending") + (B2:B100 = "West")) * 1 )

If there are 10 rows with “Pending” and 8 rows with “West”, but 3 rows are both, the formula returns 15 (10+8−3). It correctly counts each row only once, even if it meets both criteria.

Using COUNTIFS with Array Constants

COUNTIFS normally applies AND logic, but you can force OR logic by giving it an array of criteria:

=SUM(COUNTIFS(range, {"crit1","crit2"}))

This works for a single column. For example, to count cells in A2:A100 that are “Pending” or “Shipped”:

=SUM(COUNTIFS(A2:A100, {"Pending","Shipped"}))

The COUNTIFS returns an array of two counts, and SUM adds them. This method is simpler than SUMPRODUCT when all criteria apply to the same column. However, it does not automatically handle rows that meet both criteria in different columns—you would need to subtract overlaps manually.

When to Use Which Method

• Use SUMPRODUCT when criteria are in different columns and you want automatic de-duplication of rows that meet multiple criteria.
• Use SUM(COUNTIFS(…)) when all criteria are in the same column and you do not need to worry about overlaps.
• Use COUNTIFS with multiple separate criteria ranges only when you want AND logic, not OR.

Common Mistakes and Pitfalls

• Forgetting to multiply by 1 in SUMPRODUCT—if you don’t, the formula may return 0 because TRUE/FALSE are not summed correctly.
• Using COUNTIFS with multiple criteria ranges when you actually want OR—this gives AND logic and undercounts.
• Mismatched range sizes—all ranges in SUMPRODUCT must be the same size, or you get a #VALUE! error.
• Hardcoding criteria—if the criteria change, it’s better to reference cells containing the values.
• Not accounting for blank cells—a blank cell will never match a text criteria, but could match an empty string “” if you use that.

Alternatives: SUMPRODUCT vs. COUNTIFS vs. PivotTables

Besides formulas, you can use a PivotTable to count rows with OR criteria by adding the fields to the Filters area and selecting multiple items. This is visual and easy to update, but not as flexible for dynamic dashboards. Another alternative is the FILTER function in Excel 365, which can return the matching rows and then you can count them with ROWS(). For example: =ROWS(FILTER(A2:B100, (A2:A100="Pending")+(B2:B100="West"))). This is more readable but requires a dynamic array version of Excel.

Frequently Asked Questions

How do I count rows with OR criteria in two different columns without double-counting?

Use the SUMPRODUCT formula with addition and multiply by 1. It automatically counts a row once even if it meets both criteria, because the sum for that row is 1 or 2, but multiplying by 1 does not change the count—actually, the formula sums the 1s, so if a row meets both, it contributes 2, not 1. To avoid double-counting, you need to use a slightly different approach: =SUMPRODUCT( ( (A2:A100="Pending") + (B2:B100="West") ) > 0 ) * 1. The >0 converts any positive sum to TRUE, then multiply by 1. This ensures each row is counted at most once.

Can I use COUNTIFS with OR logic in a single column?

Yes, by using an array constant like =SUM(COUNTIFS(A2:A100, {"Pending","Shipped"})). This works in all Excel versions, but in older versions you may need to press Ctrl+Shift+Enter to enter it as an array formula.

Why does my SUMPRODUCT formula return #VALUE!?

This usually happens when the ranges in the formula have different dimensions. For example, if you use A2:A100 and B2:B99, Excel cannot align them. Ensure all ranges have the same number of rows and columns.

How do I count rows where a cell is not blank OR another cell equals a value?

Use =SUMPRODUCT( ( (A2:A100<>"") + (B2:B100="Yes") ) > 0 ) * 1. The <>"" checks for non-blank cells. Remember to multiply by 1 after the comparison.

Does this work in Google Sheets?

Yes, the same formulas work in Google Sheets. SUMPRODUCT and COUNTIFS behave similarly, and array constants are supported. The FILTER function also works in Google Sheets.

What if I have more than two criteria?

You can add as many conditions as you need inside the SUMPRODUCT parentheses, separated by +. For example, =SUMPRODUCT( ( (A2:A100="A") + (B2:B100="B") + (C2:C100="C") ) > 0 ) * 1.

Related

• Over the Hill: Wheel, Pedals & Multiplayer Support
• DIRECTV NOW & AT&T TV: How Many Streams?
• Why I Never Update My 3D Printer Firmware Right Away

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2