Text
Check if cell contains specific words but NOT others (SEARCH, ISNUMBER & NOT)
Match product SKUs, support tickets, or billing descriptions that include required keywords while excluding disqualifying terms like "Trial" or "Refund".
Combine positive keyword inclusion and negative keyword exclusion in one clean test!
This example tests whether a description contains Cloud and contains neither Trial nor Refund. It is a text-routing rule, not proof that a transaction is paid or eligible for revenue recognition.
SEARCH checks case-insensitive substrings, not complete words: Cloudless also contains Cloud. SEARCH supports wildcard patterns such as * and ?; this example deliberately treats the three inputs as SEARCH patterns. Use literal escaping when wildcard characters should be matched literally.
Require nonempty descriptions and patterns before calculating. Blank SEARCH patterns can otherwise match unexpectedly. This fixed helper range covers four rows only, and changes outside it are not automatically counted.
| Billing Line Description (A) | Has "Cloud"? | Excludes "Trial" & "Refund"? | Matches text rule? |
|---|---|---|---|
| Enterprise Cloud Annual Plan | TRUE (pos 12) | TRUE (neither found) | TRUE (1) |
| Enterprise Cloud Free Trial | TRUE (pos 12) | FALSE ("Trial" found!) | FALSE (0) |
| Starter Desktop Annual Plan | FALSE (no "Cloud") | TRUE | FALSE (0) |
| Cloud Refund Credit Memo | TRUE (pos 1) | FALSE ("Refund" found!) | FALSE (0) |
Case-insensitive include + exclude test with AND, ISNUMBER & SEARCH
Requires C2 ("Cloud") to appear in A2 while disqualifying rows containing D2 ("Trial") or E2 ("Refund"). Switch SEARCH to FIND if capitalization must match.
=IF(OR(COUNTBLANK(A2)>0,COUNTBLANK(C2:E2)>0),"Invalid inputs",AND(ISNUMBER(SEARCH($C$2, A2)), NOT(ISNUMBER(SEARCH($D$2, A2))), NOT(ISNUMBER(SEARCH($E$2, A2)))))Count qualified rows across a helper flag column
Coercing the boolean test with --(...) in column B lets SUM(B2:B5) tally matching records cleanly.
=IF(OR(COUNTBLANK(A2:A5)>0,COUNTBLANK(C2:E2)>0,COUNT(B2:B5)<>4),"Invalid inputs",SUM(B2:B5))Try the example
Use the original values or change them to test your understanding.