Summing & Aggregation
Sum If Begins With Prefix or Ends With Suffix (Wildcard SUMIFS Formula)
Total general-ledger accounts or SKU codes starting with a prefix (Prefix & "*") or ending with a regional/currency suffix ("*" & Suffix)—or matching BOTH simultaneously—using wildcard SUMIFS.
Roll up GL account prefixes ("REV-*") and regional suffixes ("*-EUR") without helper columns!
Enterprise chart-of-accounts strings and warehouse SKU codes pack structured hierarchy into prefixes and suffixes—such as "REV-4100-USD", "REV-4200-EUR", "COGS-5100-USD", and "REV-4300-EUR"! Instead of splitting every code into three helper columns first, SUMIFS natively supports anchored asterisk (*) wildcards on text codes!
Notice where you place the asterisk: Prefix & "*" (such as "REV-*") anchors at the LEFT edge so ONLY strings beginning with "REV-" match; "*" & Suffix (such as "*-EUR") anchors at the RIGHT edge so ONLY strings ending with "-EUR" match; and passing BOTH criteria pairs inside SUMIFS(B2:B5, A2:A5, D2 & "*", A2:A5, "*" & D3) totals rows that start with "REV-" AND end with "-EUR" in a single formula! (Important gotcha: SUMIFS wildcards only match text strings—if your GL codes are pure numbers like 4100, convert them to text or use numeric bounds >=4000 and <5000!)
| GL Account String (Col A) | Posted Amount (Col B) | Matches Prefix "REV-"? | Matches Suffix "-EUR"? |
|---|---|---|---|
| REV-4100-USD | $1,000 | Yes (Begins with REV-) | No (Ends with -USD) |
| REV-4200-EUR | $2,000 | Yes (Begins with REV-) | Yes (Ends with -EUR) -> Both! |
| COGS-5100-EUR | $3,000 | No (Begins with COGS-) | Yes (Ends with -EUR) |
| REV-4300-EUR | $4,000 | Yes (Begins with REV-) | Yes (Ends with -EUR) -> Both! |
Sum Rows Where Text Code Begins With Prefix (Prefix & "*")
Appends trailing wildcard "*" to prefix D2 so only strings starting with D2 are summed.
=IF(D2 = "", "Enter Prefix", SUMIFS(B2:B5, A2:A5, D2 & "*"))Sum Rows Where Text Code Ends With Suffix ("*" & Suffix)
Prepends leading wildcard "*" to suffix D3 so only strings ending with D3 are summed.
=IF(D3 = "", "Enter Suffix", SUMIFS(B2:B5, A2:A5, "*" & D3))Combined Prefix AND Suffix Wildcard SUMIFS
Applies both anchored wildcards simultaneously on A2:A5.
=SUMIFS(B2:B5, A2:A5, D2 & "*", A2:A5, "*" & D3)Try the example
Use the original values or change them to test your understanding.