How to Sumproduct Count Multiple Or Criteria in Excel

Why This Task Matters in Excel

Imagine you manage a product catalogue, a customer database, or a project task list. Very often you will need to answer questions such as:

• How many orders were placed in either Canada or Mexico last quarter?
• How many employees work in Sales, Marketing, or Finance?
• How many support tickets are marked “Open” or “Pending” right now?

All three questions require a single count of records that meet one of several possible criteria—an “OR” relationship. Doing this manually is tedious and error-prone. If you filter “Canada,” write down the count, filter “Mexico,” write down that count, and finally add the two numbers together, you waste time and risk overlooking updates when your data changes.

Excel’s power comes from automating such logic with formulas that recalculate instantly. In business intelligence, supply-chain analysis, finance, HR reporting, or any data-driven field, fast, reliable counts drive dashboards, KPIs, and decisions. Not mastering this ability means:

• Reports are delivered late because someone must “eyeball” filters.
• Dashboards display outdated numbers because counts are hard-wired.
• Stakeholders lose trust when totals don’t match parallel systems.

While several Excel functions can tackle OR criteria, SUMPRODUCT remains the most flexible, robust, and version-independent approach. It handles:

• Multiple OR criteria on the same field.
• Combinations of AND plus OR logic in a single formula.
• Dynamic ranges without helper columns.
• Numeric, text, logical, or even date comparisons.

SUMPRODUCT works equally well in classic Excel (2007–2019) and Microsoft 365. For analysts who must share workbooks across mixed environments, that compatibility is critical. Neglecting to learn SUMPRODUCT leaves you scrambling for ad-hoc work-arounds, complicating larger models and slowing analysis. When you master the pattern once, you can scale it to far more complex scenarios—filtering by customer type, region, and product category in a single cell—linking seamlessly to charts, slicers, or Power Query outputs.

Best Excel Approach

The most reliable pattern for “count with multiple OR criteria” is SUMPRODUCT + Boolean addition:

=SUMPRODUCT(--(((Range=Criteria1)+(Range=Criteria2)+(Range=Criteria3))>0))

Why this works:

1. Each comparison (Range=Criteria) creates a TRUE/FALSE array.
2. Adding those arrays converts OR logic to math. If any condition is TRUE for a row, the sum for that row exceeds zero.
3. Comparing the sum to zero (>0) converts the result back into TRUE/FALSE—now reflecting overall OR logic.
4. The double unary operator (--) turns TRUE/FALSE into 1/0, allowing SUMPRODUCT to add them.

When to use?

• Whenever you need to keep the workbook compatible with any version of Excel.
• When AND and OR conditions must co-exist in one formula.
• When you prefer a single-cell solution without helper columns or array-entering.

Prerequisites:

• Data stored in contiguous ranges (tables are ideal).
• No empty rows within the set (blanks are fine inside columns).
• Basic understanding of relative vs absolute references for copying formulas.

Syntax breakdown for three criteria (A2:A100 is the field being tested):

=SUMPRODUCT(--(((A2:A100="Red")+(A2:A100="Blue")+(A2:A100="Green"))>0))

Alternative (two criteria):

=SUMPRODUCT(--(((A2:A100="Canada")+(A2:A100="Mexico"))>0))

Parameters and Inputs

Field (Range)

• Required. The column you are testing, e.g. [A2:A100] or Table1[Region].
• Must be the same size for every array inside SUMPRODUCT.

Criteria (Criteria1, Criteria2, …)

• Required. Each value you want to count. Can be text (“Open”), numbers (5), dates (DATE(2023,3,31)), or logical tests (\">=100\").
• Case-insensitive for text comparisons with =.

Optional: Additional AND conditions

• You can multiply arrays to force AND logic. For example, add *(B2:B\100=\"Q1\") after the big parentheses.
• Multiplying preserves row alignment, so ensure ranges line up.

Data Preparation

• Remove leading/trailing spaces to avoid mismatches.
• Store dates as true Excel dates.
• Avoid mixed data types (text “100” vs numeric 100).

Validation

• Check that each range has the same number of rows, otherwise SUMPRODUCT returns a #VALUE! error.
• Use Data Validation to restrict inputs to allowed values, reducing unexpected blanks or typos.

Edge Cases

• Counting blanks? Use (Range=\"\") in the comparison.
• Looking for numbers within a band? Use (Range>=Lower)*(Range<=Upper) for AND, or add those tests for OR.

Step-by-Step Examples

Example 1: Basic Scenario – Counting Shirt Colors

Data setup
In [Sheet1] place the following list in [A1:B11]:

Goal: How many entries are either “Red” or “Blue”?

Steps

1. Click [D2] (or any empty cell).
2. Enter:

=SUMPRODUCT(--(((A2:A11="Red")+(A2:A11="Blue"))>0))

3. Press Enter.

Expected result
7 – because “Red” appears 3 times and “Blue” 3 times, totaling 6, plus one more “Blue” at row 10 equals 7.

Why it works

• (A2:A\11=\"Red\") returns [TRUE, FALSE, FALSE, TRUE, …].
• (A2:A\11=\"Blue\") returns [FALSE, TRUE, FALSE, FALSE, …].
• Adding them yields [1,1,0,1,0,1,0,0,1,1].
• >0 converts to TRUE where at least one criterion matched.
• Double unary converts TRUE to 1, FALSE to 0, then SUMPRODUCT adds them.

Variations

• Swap criteria easily: change “Red” to a cell reference (e.g., $F$2) to make the formula dynamic.
• Count blanks and one color: use ((A2:A\11=\"\")+(A2:A\11=\"Yellow\"))>0.

Troubleshooting
If you see #VALUE!, confirm A2:A11 in both comparisons covers exactly 10 rows. Using structural references in Tables avoids such mismatches.

Example 2: Real-World Application – Customer Orders by Country

Scenario: An international company tracks orders in a Table named Orders. Columns are Date, Country, Amount. Management asks: “How many orders did we ship to either Canada or Mexico in fiscal year 2023?”

Data (4600 rows). We will simplify with a miniature view:

Steps

1. Ensure the data range is formatted as an Excel Table (Ctrl + T) with name Orders.
2. In a summary sheet, set [B2] = DATE(2023,1,1) and [B3] = DATE(2023,12,31).
3. In [B5] enter the core formula:

=SUMPRODUCT(
    --( (Orders[Country]="Canada") + (Orders[Country]="Mexico") > 0 ),
    --(Orders[Date]>= $B$2 ),
    --(Orders[Date]<= $B$3 )
)

Explanation

• First argument implements OR across Country.
• Second and third arguments create an AND date range filter using multiplication (implicit AND).
• SUMPRODUCT multiplies the three 0/1 arrays row by row, keeping only rows that meet the date AND (Canada OR Mexico) logic.

Result
If 965 orders satisfy the combined criteria, 965 appears instantly, and it updates automatically when new orders are appended to the table.

Business impact

• Dashboard KPI “North American Export Orders” updates in real time.
• No helper columns cluttering the Orders sheet.
• Works in Excel 2010 at a branch office and Microsoft 365 in HQ, so everyone sees the same number.

Integration
Feed the formula result into a Gantt chart of shipping capacity, or use it as a denominator for on-time delivery percentage.

Performance
For 4600 rows, SUMPRODUCT is instantaneous. For 200k rows, consider referencing only the used portion of the table (structured references already help) or move calculations to Power Pivot.

Example 3: Advanced Technique – Dynamic OR List + Wildcards

Scenario: Marketing wants to know how many email addresses end with any of five different domains stored in a named range Domains [E2:E6]. They also want the ability to add more domains next month without editing the formula.

Data

• Emails in column B, [B2:B50000].
• Domains list in column E: “gmail.com”, “outlook.com”, “yahoo.com”, “corp.co.uk”, “edu.org”.

Challenge
We need OR criteria with wildcards (*gmail.com) and dynamic length (Domains range may grow).

Solution
Instead of COUNTIF per domain, use an array inside SUMPRODUCT combined with ISNUMBER + SEARCH:

=SUMPRODUCT(
    --(MMULT(--ISNUMBER(SEARCH(Domains, B2:B50000)), TRANSPOSE(COLUMN(Domains)^0)) > 0)
)

How it works

1. SEARCH(Domains, B2:B50000) uses implicit intersection in Microsoft 365 to return a 2-D array (rows = emails, columns = domains). In legacy Excel, you wrap it in TRANSPOSE if necessary.
2. ISNUMBER converts found positions to TRUE/FALSE.
3. The double unary turns those into 1/0.
4. MMULT collapses the multiple OR columns into a single column by summing across domains.
5. Test greater than 0: any positive total means at least one domain matched.
6. SUMPRODUCT adds the 1’s.

Edge-case handling

• SEARCH is case-insensitive; use FIND for case-sensitive.
• Missing values produce errors; wrap SEARCH in IFERROR if required.

Performance tips

• For 50k emails × 10 domains = 500k comparisons; still reasonable in Microsoft 365 on modern hardware.
• Restrict B2:B50000 to an Excel Table so the spill range adapts automatically and no wasted blanks exist.

Professional takeaway
You get an enterprise-grade, fully dynamic “Contains any of these substrings” counter—something COUNTIFS cannot do in one shot.

Tips and Best Practices

1. Convert to Tables early – Structured references like Orders[Country] resize automatically and prevent mismatched ranges.
2. Anchor criteria cells – Replace hard-coded text with references ($F$2) to make dash­boards interactive and formulas maintainable.
3. Debug with F9 – Highlight a sub-array in the formula bar and press F9 to inspect the TRUE/FALSE or 1/0 results.
4. Minimize range size – Point at specific used rows (or whole Tables) rather than entire columns, boosting calculation speed.
5. Document logic – Add a comment above complex SUMPRODUCT formulas describing the AND/OR relationships for future maintainers.
6. Stay version aware – If every user is on Microsoft 365, consider FILTER or LET for readability, but default to SUMPRODUCT when in doubt.

Common Mistakes to Avoid

1. Unequal Range Lengths
   • Symptom: #VALUE! error.
   • Fix: Check each array in SUMPRODUCT covers the exact same rows.
2. Forgetting the greater than 0 Test
   • Symptom: Only counts rows matching all criteria, not any.
   • Fix: Wrap the added comparisons in parentheses and compare to zero.
3. Neglecting Double Unary
   • Symptom: Formula returns 0 even when matches exist.
   • Fix: Prepend -- to each Boolean array or to the final combined array.
4. Hard-coding Case Variations
   • Symptom: “Red” counts, “red” does not.
   • Fix: Standardize text with UPPER or LOWER in a helper column, or use case-insensitive comparisons.
5. Wildcards with = Operator
   • Symptom: Counts fail for partial matches.
   • Fix: Use SEARCH or COUNTIF with \"\"&criteria&\"\" pattern instead of equality tests.

Alternative Methods

Migration tips

• Start with SUMPRODUCT for compatibility. When your organization upgrades to Microsoft 365, experiment with FILTER and LET, documenting changes.
• If data grows beyond 1 million rows, move the counting step into Power Query or Power BI and link the output back into Excel.

FAQ

When should I use this approach?

Use SUMPRODUCT when you have multiple OR criteria, possibly combined with AND filters, and require a single, transferable formula that works from Excel 2010 through Microsoft 365.

Can this work across multiple sheets?

Yes. Reference ranges with sheet names: 'Jan Orders'!A2:A100. Ensure every referenced range has identical row counts. If ranges differ, pull them into a consolidated sheet or Table first.

What are the limitations?

SUMPRODUCT tops out at a little under 1 million rows—the row limit of a worksheet. It also recalculates every time any referenced cell changes, which may slow very large models.

How do I handle errors?

Wrap risky portions in IFERROR. Example: --(IFERROR(SEARCH(Domains, Emails),0)>0). Always test formulas on a copy of data before rolling into production.

Does this work in older Excel versions?

Yes. The Boolean-math SUMPRODUCT pattern is compatible back to Excel 2003. However, structured Table references require Excel 2007+. Users on 2003 must revert to plain ranges.

What about performance with large datasets?

Limit ranges, avoid whole-column references, and consider turning off automatic calculation until edits are complete. On Microsoft 365, =LET() can store sub-arrays to reduce recomputation.

Conclusion

Counting records that meet any of several criteria is a common analytical hurdle. By mastering the SUMPRODUCT Boolean pattern, you unlock a version-agnostic, lightning-fast solution that scales from simple color tallies to multi-dimensional business dashboards. The technique integrates seamlessly with Tables, charts, and downstream models, forming a cornerstone skill for every serious Excel practitioner. Keep experimenting—replace hard-coded values with cell references, add AND filters, or try dynamic domain lists—and you will soon wield SUMPRODUCT with the same ease as basic arithmetic.