Conditional Aggregation
Count Cells That Contain Either X or Y Without Double-Counting Overlaps
Count text cells containing substring X or substring Y accurately by subtracting the dual-match intersection COUNTIFS("*X*","*Y*") from COUNTIF("*X*") + COUNTIF("*Y*").
Prevent naive COUNTIF + COUNTIF from double-counting cells with both keywords!
Adding COUNTIF(range, "*" & X & "*") + COUNTIF(range, "*" & Y & "*") overcounts whenever a single cell contains BOTH keywords (for example, "RUSH-SEA-03" matches both "*RUSH*" and "*SEA*")! By the principle of inclusion-exclusion, the exact count of cells containing X OR Y is COUNTIF(range, "*" & X & "*") + COUNTIF(range, "*" & Y & "*") - COUNTIFS(range, "*" & X & "*", range, "*" & Y & "*").
On an individual row, pair ISNUMBER(SEARCH(X, cell)) and ISNUMBER(SEARCH(Y, cell)) inside OR(...) to tag matching records with a clean "Match" vs "Skip" helper flag.
| Shipment Tag | Matches "RUSH"? | Matches "SEA"? | Counted Once? |
|---|---|---|---|
| RUSH-AIR-01 | Yes | No | Yes (1) |
| STD-SEA-02 | No | Yes | Yes (2) |
| RUSH-SEA-03 | Yes | Yes (Overlap -1) | Yes (3 — not 4!) |
| ECO-RAIL-04 | No | No | No |
| RUSH-ROAD-05 | Yes | No | Yes (4 total) |
De-Duplicated Count of Cells Containing Substring X or Y
Counts rows in A2:A6 containing B7 ("RUSH") or B8 ("SEA") while subtracting rows containing both.
=IF(OR(ISBLANK(B7), LEN(TRIM(B7)) = 0, ISBLANK(B8), LEN(TRIM(B8)) = 0), "Enter Both Keywords", COUNTIF(A2:A6, "*" & B7 & "*") + COUNTIF(A2:A6, "*" & B8 & "*") - COUNTIFS(A2:A6, "*" & B7 & "*", A2:A6, "*" & B8 & "*"))Row-Level Either-Keyword Flag (Row A4)
Flags whether cell A4 contains case-insensitive substring B7 or substring B8.
=IF(OR(ISNUMBER(SEARCH(B7, A4)), ISNUMBER(SEARCH(B8, A4))), "Match", "Skip")Try the example
Use the original values or change them to test your understanding.