Math
Sum If Cells Contain Text from Another Cell ("*" & Keyword & "*")
Total invoices or bank transactions whose description contains a dynamic search keyword in E2 using wildcard concatenation "*" & E2 & "*"—with an empty-search-box ISBLANK guard so blank inputs do not sum the entire sheet!
Beware the blank-cell wildcard trap: "*" & "" & "*" = "**" matches EVERYTHING!
To sum all bank-feed descriptions in A2:A6 that contain whatever vendor keyword a user types into cell E2 (e.g., "AWS" matching "AMZN AWS US-EAST-1" and "AWS Route53 DNS"), wrap E2 between asterisk wildcards: =SUMIF(A2:A6, "*" & E2 & "*", B2:B6)!
Watch out for a famous dashboard bug: if the user clears the search box E2 so E2 is blank (""), "*" & E2 & "*" evaluates to "**"—which matches EVERY non-empty text cell on the sheet and reports the entire ledger total! Wrapping with =IF(LEN(TRIM(E2)) = 0, 0, SUMIF(A2:A6, "*" & TRIM(E2) & "*", B2:B6)) makes interactive keyword search boxes 100% bulletproof!
| Keyword Cell E2 | Naive =SUMIF(A2:A6, "*"&E2&"*", B2:B6) | Guarded Formula Output | Why It Matters |
|---|---|---|---|
| "AWS" | $340 + $85 = $425 | $425 | Matches substring anywhere in cell |
| "Stripe" | $190 | $190 | Case-insensitive partial match |
| "" (Cleared Search Box!) | $1,035 (Sums ALL text rows!) | $0 (Cleanly zeroed!) | Prevents "**" matching everything! |
Blank-Safe Dynamic Substring Sum (IF + LEN + TRIM + Wildcard SUMIF)
Trims accidental spaces around keyword E2, returns 0 when E2 is empty, and sums partial-text matches via "*" & TRIM(E2) & "*".
=IF(LEN(TRIM(E2)) = 0, 0, SUMIF(A2:A6, "*" & TRIM(E2) & "*", B2:B6))Multi-Criteria Substring + Account Filter via SUMIFS
Combines partial-keyword matching on A2:A6 with exact CostCenter filtering on C2:C6.
=IF(LEN(TRIM(E2)) = 0, 0, SUMIFS(B2:B6, A2:A6, "*" & TRIM(E2) & "*", C2:C6, F2))Try the example
Use the original values or change them to test your understanding.