Math & Trig

Sum If Cells Contain Both Keywords (AND) or Either Keyword (OR)

Sum rows containing both or either literal text substrings, escape wildcard characters, and avoid double-counting the intersection without silently accepting empty keywords.

Literal substring matching with AND and OR

D2 and E2 contain nonempty text substrings. Matching is case-insensitive and not limited to whole words: EU can match within a longer word. Spaces are meaningful and are not automatically trimmed.

For Cloud and EU, the intersection contains 1000+400=1400. Cloud totals 2000 and EU totals 2200. Adding those totals counts the intersection twice; subtracting it once gives the union total 2800.

The wildcard wrappers implement contains, while characters inside each keyword are escaped. A keyword * means an actual asterisk, not every description. Escape ~ first, followed by * and ?.

All five amounts must be numeric. Zero and negative adjustments are valid; missing or text amounts are reported. The union counts each matching row once, but does not remove duplicate ledger rows or repeated transactions. Identical keywords still count each matching row once.

Total where both substrings occur

SUMIFS applies both criteria to the same description column. An empty keyword is rejected instead of matching nearly everything.

=IF(OR(NOT(ISTEXT(D2)),NOT(ISTEXT(E2)),D2="",E2=""),"Enter two nonempty text keywords",IF(COUNT(B2:B6)<>5,"All amounts must be numeric",SUMIFS(B2:B6,A2:A6,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?")&"*",A2:A6,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"~","~~"),"*","~*"),"?","~?")&"*")))

Total where either substring occurs

Inclusion-exclusion sums the two sets and subtracts their overlap. It also works for signed amounts because inclusion is determined by the descriptions.

=IF(OR(NOT(ISTEXT(D2)),NOT(ISTEXT(E2)),D2="",E2=""),"Enter two nonempty text keywords",IF(COUNT(B2:B6)<>5,"All amounts must be numeric",SUMIF(A2:A6,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?")&"*",B2:B6)+SUMIF(A2:A6,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"~","~~"),"*","~*"),"?","~?")&"*",B2:B6)-SUMIFS(B2:B6,A2:A6,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?")&"*",A2:A6,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"~","~~"),"*","~*"),"?","~?")&"*")))

Try the example

Use the original values or change them to test your understanding.