How to Sum If Cells Contain Either X Or Y in Excel

Why This Task Matters in Excel

Imagine you manage sales transactions where every product description contains a brand code. Your manager asks for the combined revenue from two key brands—say “Alpha” or “Beta”—without manually filtering the data. Or suppose you track customer support tickets and want to know how many minutes were spent on calls flagged as either “Priority” or “Escalated.” In financial modeling, you might regularly roll up costs for departments containing the words “Marketing” or “Sales.”

In all these scenarios, the records you need are identified not by an exact match but by the presence of certain text fragments. Manually filtering works for small lists, yet it quickly becomes risky and time-consuming in growing workbooks. Automating the process with an Excel formula ensures consistency, reduces oversight, and allows rapid what-if analysis.

Excel excels (pun intended) at data aggregation, and combining its text-searching functions with conditional summing unlocks a flexible solution. The task connects to broader skills such as dynamic dashboards, PivotTables, and error-proof reporting pipelines. Mastering conditional OR logic will also prepare you for tasks that involve more complex queries, for example “Sum if the description contains any of three terms and the date falls in the current month.” Conversely, ignorance of these techniques leads to tedious manual work, possible misstatements in financial reports, and dashboards that break when data updates.

Best Excel Approach

In most modern spreadsheets, the fastest, most transparent approach combines two independent SUMIF calculations—one for “X” and one for “Y”—then adds their results. It leverages the fact that SUMIF already performs a contains search when wildcards are used, and it remains backward compatible to Excel 2003.

=SUMIF([A:A],"*X*",[B:B]) + SUMIF([A:A],"*Y*",[B:B])

• [A:A] The range you want to inspect for the text fragment
• "*X*" Contains criteria for text X (asterisks act as wildcards)
• [B:B] The numeric range you want to sum

When to use this approach:

• You are dealing with only two search terms (or a small handful)
• Simplicity and auditability are top priorities
• Your workbook must run in very old Excel versions

If your OR list is large, or if you need to avoid double-counting the same row matching both terms, switch to SUMPRODUCT with the SEARCH function wrapped in ISNUMBER. It evaluates each criterion and ensures each row is added once even if it contains both X and Y.

=SUMPRODUCT( ([B:B]) * ( (ISNUMBER(SEARCH("X",[A:A])) + ISNUMBER(SEARCH("Y",[A:A])) ) > 0 ) )

Parameters and Inputs

• Criterion Range — Usually a text column such as product names in [A2:A5000]. Must be in the same number of rows as the Sum Range.
• Sum Range — Any numeric column such as amounts in [B2:B5000]. If it contains blanks, Excel treats them as zeros.
• Text Fragments — Case-insensitive in SEARCH, case-sensitive in FIND. Wildcards in SUMIF treat upper- and lower-case alike.
• Wildcards — Use asterisks to signify “anything,” e.g., "*X*" finds “X100,” “Deluxe,” and “Mix.”
• Data Preparation — Trim leading/trailing spaces, convert numbers stored as text, and spell criteria consistently so your searches succeed.
• Edge Cases — Empty cells in the Criterion Range evaluate as no match. Non-numeric cells in the Sum Range cause #VALUE! in array formulas but are safely ignored by SUMIF.

Step-by-Step Examples

Example 1: Basic Scenario

You have a toy revenue table:

Goal: Sum revenue where the product contains either “Alpha” or “Beta.”

1. Enter the data in [A2:B6].
2. In [D2] type the formula:

=SUMIF([A2:A6],"*Alpha*",[B2:B6]) + SUMIF([A2:A6],"*Beta*",[B2:B6])

3. Result: 1200 + 900 + 1500 + 600 = 4200.
4. Logic: Each SUMIF scans [A2:A6] and adds the corresponding [B] value whenever the wildcard pattern matches.
5. Variation: Add a third term by extending with another + SUMIF(...).
   Troubleshooting: If you get zero, verify that your criteria are spelled exactly as they appear in the cells (case does not matter, but extra spaces do).

Example 2: Real-World Application

Scenario: A customer-service log records ticket category and time spent. You need the total minutes invested in tickets flagged as either “Priority” or “Escalated” this quarter.

Because some rows contain both keywords (“Priority – Technical”), we must avoid double-counting. Use SUMPRODUCT:

=SUMPRODUCT( ([C2:C7]) * ( ( ISNUMBER(SEARCH("Priority",[B2:B7])) + ISNUMBER(SEARCH("Escalated",[B2:B7])) ) >0 ) )

Explanation:

1. SEARCH("Priority",[B2:B7]) returns position numbers when found or #VALUE! if not.
2. ISNUMBER(...) converts those positions into TRUE/FALSE, which Excel coerces to 1 or 0.
3. We add the two arrays, so any row containing at least one term yields a value of 1 or 2.
4. We test “greater than 0” to convert everything into a single 1/0 flag, preventing double-counting.
5. SUMPRODUCT multiplies the flag by [C] minutes and sums the result in one pass, giving 12 + 35 + 22 + 40 = 109 minutes.

Integrations: This formula feeds neatly into a KPI dashboard, and because it is array-based, you can wrap it in a named formula or spill it into multiple scenarios with LET for improved readability on Excel 365.

Example 3: Advanced Technique

Suppose you operate a global expense workbook with millions of rows in Power Query or Power Pivot. You need to sum costs where the description contains any of five marketing campaign codes stored in a helper range [H1:H5]. Using an explicit SUMIF for each code is tedious, and SUMPRODUCT may be slow. Instead, construct a dynamic pattern in a helper column, then aggregate in a PivotTable or by using FILTER plus SUM.

1. Populate [H1:H5] with your campaign codes: X, Y, Z, M, N.
2. In [D2] add a flag formula with a regular expression-like approach using TEXTJOIN:

=--ISNUMBER(SEARCH(TEXTJOIN("|",TRUE,$H$1:$H$5),A2))

3. Drag down; [D:D] now shows 1 for any row whose description contains at least one code.
4. Finally, sum the costs via:

=SUMIFS([B:B],[D:D],1)

In Excel 365 you can skip the helper column:

=SUM(FILTER([B2:B100000],ISNUMBER(SEARCH(TEXTJOIN("|",TRUE,$H$1:$H$5),[A2:A100000]))))

Performance Tips:

• FILTER uses the new dynamic array engine, much faster on large datasets.
• Power Pivot or the Data Model allows the same logic via DAX using CONTAINSSTRING() combined with SUMX() for enterprise-grade datasets exceeding a million rows.

Edge Cases: Confirm that none of the codes are substrings of each other, which could lead to unintended matches (“X” would also match “Excel”). Prepend or append delimiters if necessary.

Tips and Best Practices

1. Pre-clean your text column with TRIM and CLEAN (or Power Query’s Transform → Trim) to remove hidden characters that derail searches.
2. Store repeated criteria such as “X” and “Y” in named ranges. It simplifies maintenance and reduces the chance of typos.
3. For dashboards, wrap lengthy SUMPRODUCT formulas inside LET to assign readable variable names and avoid recalculating the same array twice.
4. Add conditional formatting to the Criterion Range to visually confirm which rows are counted; use the same SEARCH logic inside the formatting rule.
5. When performance matters, push calculations to the Data Model (Power Pivot) and write a DAX measure—aggregation is columnar and highly optimized.
6. Document your OR logic next to the data or in cell comments so future users understand why the formula looks complicated.

Common Mistakes to Avoid

1. Forgetting Wildcards in SUMIF: Writing "X" instead of "*X*" looks for an exact match and returns zero. Always inspect criteria for the necessary asterisks.
2. Double-Counting with SUMIF + SUMIF: If the same row can contain both X and Y, it will be added twice. Switch to SUMPRODUCT with a single evaluation flag.
3. Misaligned Ranges: [A2:A100] and [B2:B99] cause mismatches and sometimes #VALUE!. Ensure all ranges cover identical rows.
4. Hidden Spaces or Non-Breaking Spaces: “Beta” typed with a trailing space fails to match "*Beta*". Use CLEAN or SUBSTITUTE to normalize.
5. Large OR Lists in SUMIF: Adding 10 or more SUMIF calls bloats calculation time. Consolidate criteria in one helper column or use array-based logic inside SUMPRODUCT or FILTER.

Alternative Methods

Key decision factors: Excel version, dataset size, number of criteria, and need for traceability.

FAQ

When should I use this approach?

Use it whenever you need a live total that updates automatically as new records appear and the defining condition is “description contains X or Y.” Ideal for sales, inventory, support logs, and project tracking.

Can this work across multiple sheets?

Yes. Point each range to its sheet, e.g., SUMIF(Data!A:A,"*X*",Data!B:B) + SUMIF(Data!A:A,"*Y*",Data!B:B) or use 3-D references in more advanced setups. With SUMPRODUCT, wrap each sheet in its own expression and add the results.

What are the limitations?

SUMIF wildcards cannot handle case-sensitive matching. SUMPRODUCT can be slow on 100 k + rows unless calculations are set to manual. Both fail if the workbook contains structured noise like line breaks inside cells.

How do I handle errors?

Wrap numeric arrays in IFERROR, for example:

=SUMPRODUCT( IFERROR(([B:B]),0) * ( ... ) )

Or clean the column with VALUE(A1) to ensure numbers are numbers.

Does this work in older Excel versions?

SUMIF works back to Excel 2000. SUMPRODUCT also works, but array evaluation of whole columns was introduced in Excel 2007; use explicit row ranges in older editions. Dynamic arrays (FILTER, LET, TEXTJOIN) require Excel 365 or Excel 2021.

What about performance with large datasets?

Limit ranges to used rows ([A2:A50000] instead of full columns), convert data to Excel Tables so ranges resize automatically, or shift heavy formulas to the Data Model. In Excel 365, FILTER plus SUM leverages multi-threaded calc and outperforms traditional arrays.

Conclusion

Being able to sum numbers when the companion text contains either X or Y is an everyday, yet deceptively powerful, Excel skill. You now know quick SUMIF tricks, robust SUMPRODUCT strategies, and modern dynamic-array solutions. Apply these techniques to automate reports, reduce manual filtering, and gain instant insights as data grows. Keep experimenting: add more criteria, fold the logic into PivotTables, or elevate to DAX. Mastery here cascades into cleaner, faster, and more reliable workbooks—an essential step toward Excel proficiency at any level.