How to Count Cells That Contain Either X Or Y in Excel

Why This Task Matters in Excel

Being able to count cells that contain either one word or another word is a deceptively simple requirement that shows up in virtually every business sector. Picture a customer-service dashboard that stores thousands of chat transcripts. Management may want to know how many messages mention “refund” or “return” so they can evaluate how many interactions relate to product dissatisfaction. The same logic applies in marketing when analysts scan survey comments for “love” or “recommend,” or in HR when the team reviews employee feedback looking for “overtime” or “burnout.”

In finance, auditors may scan transaction descriptions for “wire” or “transfer” to flag items requiring additional review, while inventory managers might inspect product notes for “expiring” or “defective” to trigger urgent actions. Each example centers on a simple question: “How many records contain at least one of these keywords?” Excel, with its flexible formulas and instant grid-based feedback, remains the fastest, most approachable tool for answering that question without diving into full-scale database queries or specialized text-analytics software.

If you do not know how to perform this count, decisions get delayed or, worse, rely on gut feeling. Manual filtering is prone to oversight and is never sustainable on dynamic data. Furthermore, mastering this single skill unlocks many adjacent capabilities: partial-match lookups, dynamic dashboards, automated quality checks, and keyword-based alerts. When combined with PivotTables or Power Query, keyword counting becomes a cornerstone of modern self-service analytics. For these reasons, any Excel user—from junior analyst to seasoned controller—should treat “count cells that contain either X or Y” as an essential technique in their toolbox.

Best Excel Approach

The most effective solution combines the COUNTIF or COUNTIFS family with wildcards, then aggregates the results for each keyword. COUNTIF supports wildcards such as the asterisk (*) that represent “any number of characters.” By wrapping the keywords in asterisks—\"x\" and \"y\"—you tell Excel to look for the substring anywhere inside the cell. Because COUNTIF handles only one criterion at a time, the trick is to count each word separately and then add the results.

A clever shortcut introduced in modern Excel versions (Office 365 and Excel 2021) is to pass COUNTIF an array constant containing both criteria; Excel spills two results and SUM adds them, yielding a single total. This keeps the formula compact, dynamic, and easy to maintain.

=SUM(COUNTIF([A2:A1000], {"*x*","*y*"}))

When should you choose this method?

• Use it whenever you need a quick scalar total and the dataset is no larger than several hundred thousand rows.
• It works in all Excel versions (pre-O365 users simply enter two COUNTIFs and add them).
• It is transparent, easy to audit, and calculates fast because COUNTIF is optimized for text searches.

If you need case sensitivity, multiple exclusion rules, or wish to avoid double-counting cells that contain both keywords, you can switch to SUMPRODUCT or a helper column with logic. We cover those alternatives later.

Alternative Syntax for Users Without Array Support

=COUNTIF([A2:A1000],"*x*") + COUNTIF([A2:A1000],"*y*")

Both formulas yield the total count of cells that contain either x or y.

Parameters and Inputs

1. Range_to_search (Required) – A contiguous block of cells such as [A2:A1000]. It can be a single column, multiple columns, or even a named range. The cells may contain text, numbers, or mixed content.
2. Keyword1, Keyword2 (Required) – Text strings you wish to detect. They are usually supplied as literals like \"x\" and \"y\" but can also be referenced from cells, e.g., [\"\"&F1&\"\"].
3. Wildcard Format – Wrap each keyword in asterisks to capture occurrences inside longer strings. Question marks (?) match single characters, but they are rarely needed for OR counts.
4. Case Sensitivity – COUNTIF is not case sensitive; if you require case-sensitive detection, use FIND inside SUMPRODUCT or add a helper column.
5. Data Cleanliness – Trim spaces, ensure consistent encoding (especially when text is imported from other systems), and decide whether blanks or errors should be counted.
6. Edge Cases – Cells containing both keywords count once with COUNTIF because the formula evaluates each criterion separately and then sums. If double-counting is a concern, switch to a boolean OR logic using SUMPRODUCT, as demonstrated in Example 3.
7. Dynamic Arrays – In Office 365, COUNTIF can accept an array constant. In older versions, use two independent COUNTIFs or a helper column.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a small feedback log in [A1:B11] where column A lists comment IDs and column B holds the comment text.

Objective: Count comments that contain either \"refund\" or \"return\".

Steps

1. Put the list in [B2:B11].
2. In an empty cell (say D2) enter:

=SUM(COUNTIF([B2:B11], {"*refund*","*return*"}))

3. Press Enter. Office 365 immediately returns the total—let’s say 4.

Why it works

• COUNTIF with \"refund\" checks each cell for the substring “refund”.
• COUNTIF with \"return\" performs the same for “return”.
• COUNTIF generates two numbers, one for each keyword; SUM adds them.

Common variations

• If your data grows, change the range to an Excel Table (Ctrl+T) and rename it Comments[Text], then rewrite the formula as: `=SUM(`COUNTIF(Comments[Text],[\"refund\",\"return\"]))—no row adjustments are required.
• You can store the keywords in cells F1 and G1, then use \"\"&F1&\"\" so non-technical colleagues can update keywords without editing formulas.

Troubleshooting

• If the count appears wrong, check for leading/trailing spaces. Use TRIM() in a helper column or Power Query to clean data.
• Confirm that plural versions like “returns” are captured—wildcards take care of this automatically.

Example 2: Real-World Application

Scenario: A pharmaceutical company tracks adverse event reports in an Excel file with 20 000 rows. Column D (Description) stores free-text notes. Analysts must count reports that mention either “rash” or “nausea” for weekly safety summaries. They also need pivot-ready counts broken down by country.

Data Setup

• Table name: tblEvents
• Key columns: [Country], [Description]
• Sheet: “AdverseReports”

Steps

1. Convert the raw data to a Table (Ctrl+T) and name it tblEvents.
2. In cell L2 on a “Summary” sheet enter the main formula:

=SUM(COUNTIF(tblEvents[Description], {"*rash*","*nausea*"}))

3. Result appears as, for example, 384.
4. To create by-country counts, add a PivotTable based on tblEvents.
   a. Rows: Country
   b. Values: Add a custom field using the same COUNTIF logic wrapped in CALCULATE (Power Pivot) or a helper column with:

=IF(OR(ISNUMBER(SEARCH("rash",[Description])), ISNUMBER(SEARCH("nausea",[Description]))),1,0)

Add the helper column to the pivot’s Values as a Sum.

Business Impact

• Safety officers can spot geographic clusters of side effects in seconds.
• No need for external text-mining software; everything lives in Excel.

Performance Tips

• COUNTIF evaluates quickly even on 100 k rows, but if the workbook starts to lag, consider turning off automatic calculation while typing (Formulas → Calculation Options → Manual).
• Use Excel Table references so new rows auto-expand into the formula range, eliminating maintenance overhead.

Example 3: Advanced Technique (Prevent Double-Counting and Add Case Sensitivity)

Suppose the compliance team wants to count unique cells that contain “Transfer” or “transfer” but not count any cell twice, even if both keywords appear, and they insist on matching the exact case “Transfer” (capital T). Here’s an advanced SUMPRODUCT approach.

Dataset: 250 k bank transaction narratives in [A2:A250001].

Formula

=SUMPRODUCT(--((ISNUMBER(FIND("Transfer", [A2:A250001]))) + (ISNUMBER(FIND("transfer", [A2:A250001]))) > 0))

Explanation

1. FIND is case sensitive; SEARCH is not.
2. ISNUMBER returns TRUE (1) if FIND locates the keyword, FALSE (0) otherwise.
3. Adding the two booleans gives three possibilities per cell:

• 0 – matches none
• 1 – matches one keyword
• 2 – matches both, but we only need to know “present at least once.”

4. The comparison greater than 0 converts any positive value to TRUE (1).
5. SUMPRODUCT aggregates the 1s into a total count.

Optimization

• To improve performance, restrict the range to a structured Table or use dynamic array functions like FILTER before feeding into SUMPRODUCT.
• You can wrap the formula in LET to store reusable expressions and avoid recalculation.

Edge-Case Handling

• If a cell contains an error value such as #N/A, ISNUMBER(FIND()) throws an error. Avoid this by nesting FIND inside IFERROR: IFERROR(ISNUMBER(FIND(...)),0).
• For extremely large datasets (several million rows), move the logic into Power Query or Power BI, then import a summary back to Excel.

Tips and Best Practices

1. Convert to Tables Early – Structured references (e.g., tblData[Comments]) auto-expand, eliminating manual range edits.
2. Store Keywords in Named Ranges – Create a range called lstKeywords for dynamic formulas. Then use: `=SUMPRODUCT(`--(MMULT(--ISNUMBER(SEARCH(lstKeywords, tblData[Comments])), TRANSPOSE(COLUMN(lstKeywords)^0))>0)). This allows unlimited keywords with no formula edits.
3. Use Helper Columns for Transparency – If stakeholders audit your workbook, add a boolean helper column “Contains X or Y” instead of a one-line formula. That visibility reduces support calls.
4. Disable Case-Sensitive Search Unless Required – SEARCH is faster than FIND. Avoid case sensitivity unless the business question truly requires it.
5. Benchmark on Copies – Measure calculation times on a sample file before rolling out to production data.
6. Document Assumptions – Note in cell comments or a README sheet whether duplicates are allowed and what keywords are in scope; future users will thank you.

Common Mistakes to Avoid

1. Omitting Wildcards – Writing \"refund\" instead of \"refund\" counts only exact matches. If counts look surprisingly low, check your wildcard placement.
2. Counting Numeric Data Accidentally – COUNTIF treats numbers differently; if your range mixes text and numbers, ensure numbers are not falsely detected through implicit conversion.
3. Double-Counting Cells with Both Keywords – Summing two COUNTIFs overstates the total when overlap exists. Use SUMPRODUCT with boolean OR to deduplicate.
4. Case Sensitivity Misunderstanding – Assuming COUNTIF distinguishes “Return” from “return.” It does not; switch to FIND for case-sensitive needs.
5. Hard-Coding Ranges – Using [A2:A1000] in the formula but the dataset grows to row 1200, leading to under-reporting. Mitigate by converting to a Table or using dynamic named ranges.

Alternative Methods

Choose COUNTIF + SUM when you want sheer simplicity and the overlap issue is irrelevant (e.g., mutually exclusive keywords). Switch to SUMPRODUCT or Power Query for nuanced logic, scalability, or integration with a data model.

FAQ

When should I use this approach?

Use it any time you need a quick keyword count across free-text cells—survey responses, support tickets, transaction comments—without importing data into a database or specialized software.

Can this work across multiple sheets?

Yes. Wrap each sheet range in its own COUNTIF and add them, or create 3D references if the structure is identical. For example: `=COUNTIF(`Sheet1:Sheet3!B:B,\"x\")+COUNTIF(Sheet1:Sheet3!B:B,\"y\").

What are the limitations?

COUNTIF cannot apply multiple OR conditions and complex AND criteria simultaneously. It is not case sensitive and may double-count overlaps. For advanced filters, rely on SUMPRODUCT, FILTER, or Power Query.

How do I handle errors?

Wrap FIND or SEARCH inside IFERROR to convert error outputs to 0. Example: `=IFERROR(`ISNUMBER(SEARCH(\"x\",A2)),0). This prevents SUMPRODUCT from propagating #VALUE!.

Does this work in older Excel versions?

Two-COUNTIF addition works back to Excel 2003. The array constant version with SUM(COUNTIF(range,[\"x\",\"y\"])) requires Excel 365, 2021, or 2019 (in some builds).

What about performance with large datasets?

On 100 k rows, COUNTIF evaluates in under a second on modern hardware. SUMPRODUCT is slower but still acceptable. For millions of rows, import the data into Power Query or Power BI; Excel’s grid is not designed for that scale.

Conclusion

Counting cells that contain either X or Y is more than a formula trick—it is a gateway skill that helps you unlock text analytics, automated monitoring, and data-driven decision making inside Excel. By mastering COUNTIF, SUMPRODUCT, and their variants, you can scan thousands of records in a blink, create dynamic dashboards, and free yourself from manual inspection. Apply the techniques from this tutorial to your own datasets, experiment with structured Tables and Power Query, and you will quickly see how a single, well-crafted formula can elevate your entire analytics workflow.