Logical
Include and exclude text substrings with SEARCH
Flag and filter transactions that match target keywords ("Cloud" OR "SaaS") while disqualifying rows containing exclusion terms ("Refund" OR "Test").
Combine OR-inclusion with AND-NOT exclusion for clean GL & CRM rules
General-ledger categorization rules constantly need compound "(Contains A OR B) AND NOT (Contains X OR Y)" logic: e.g., tag software revenue lines containing "Cloud" or "SaaS", EXCEPT credit memos containing "Refund" or QA orders containing "Test"!
Case-insensitive SEARCH paired with ISNUMBER / ISERROR makes every row test crystal-clear: IncludeHit = (ISNUMBER(SEARCH("Cloud", A2)) + ISNUMBER(SEARCH("SaaS", A2))) > 0, and NoExcludedHit = ISERROR(SEARCH("Refund", A2)) * ISERROR(SEARCH("Test", A2)). Multiplying IncludeHit * NoExcludedHit yields 1 ONLY when at least one target keyword is present AND zero banned words appear!
| GL Description (A) | Include ("Cloud"|"SaaS") | No Exclude ("Refund"|"Test") | Final Match (C) |
|---|---|---|---|
| "Cloud Pro Annual Seat" | TRUE (1) | TRUE (1) | 1 (Matched!) |
| "Cloud Pro Refund Credit" | TRUE (1) | FALSE (0 - Refund!) | 0 (Excluded!) |
| "SaaS Enterprise Renewal" | TRUE (1) | TRUE (1) | 1 (Matched!) |
| "SaaS Sandbox Test Order" | TRUE (1) | FALSE (0 - Test!) | 0 (Excluded!) |
| "On-Site Hardware Rack" | FALSE (0) | TRUE (1) | 0 (No target word) |
Row-level Include-without-Exclusion flag and filtered revenue total
SEARCH is case-insensitive ("refund", "REFUND", and "Refund" are all caught cleanly).
These are substring rules, not whole-word matching. Cloud matches Cloudy and Test also matches Contest. This can produce false positives; choose terms for your data and review the results. Input descriptions are assumed to be text, without error values.
=SUMIFS(B2:B6, C2:C6, 1)Join matching descriptions into one text result
FILTER selects matching descriptions; TEXTJOIN combines them into one cell separated by commas. The result is not a spilled table.
=TEXTJOIN(", ", TRUE, FILTER(A2:A6, C2:C6 = 1, "None"))Try the example
Use the original values or change them to test your understanding.