Conditional Aggregation
Count & Sum Cells Ending With or Starting With Specific Text (COUNTIF / SUMIF)
Match SKU suffixes ("*" & suffix) and regional prefixes (prefix & "*") dynamically with COUNTIF and SUMIF while preventing blank-affix wildcard traps.
Anchor wildcards on ONE side only—and always guard blank search cells!
Structured part numbers like "EU-410-PRO" encode region at the start ("EU-") and product tier at the end ("-PRO"). Placing an asterisk BEFORE a suffix ("*" & E2) matches only cells ENDING with E2, whereas placing an asterisk AFTER a prefix (E3 & "*") matches only cells STARTING with E3!
Watch out for a sneaky spreadsheet trap: if user input cell E2 is accidentally left blank, "*" & E2 evaluates to "*"—which matches EVERY non-empty text cell in the entire column! Checking LEN(TRIM(E2)) = 0 first prevents accidental 100% wildcard matches!
| Affix Pattern Rule | Wildcard Expression | Result on Catalog A2:B6 | Why It Is Safe |
|---|---|---|---|
| Ends with "-PRO" (E2) | COUNTIF(A2:A6, "*" & E2) | 3 SKUs | Leading * anchors match strictly to end |
| Starts with "EU-" (E3) | SUMIF(A2:A6, E3 & "*", B2:B6) | $1,200 | Trailing * anchors match strictly to start |
| Blank / Whitespace Affix | LEN(TRIM(E2)) = 0 Guard | "Specify Affix" | Blocks bare "*" from matching all text! |
Count Cells Ending With Target Suffix ("*" & E2)
Counts SKUs in A2:A6 ending with suffix E2 after verifying E2 is non-blank.
=IF(LEN(TRIM(E2)) = 0, "Specify Affix", COUNTIF(A2:A6, "*" & E2))Sum Amounts for Cells Starting With Target Prefix (E3 & "*")
Sums revenue B2:B6 where SKU code A2:A6 begins with regional prefix E3.
=IF(LEN(TRIM(E3)) = 0, "Specify Affix", SUMIF(A2:A6, E3 & "*", B2:B6))Try the example
Use the original values or change them to test your understanding.