Lookup & Reference
Case-sensitive FILTER, SUM & COUNT using an EXACT helper flag
Use per-row EXACT flags to distinguish case variants, count matching records and sum their numeric amounts.
Why FILTER(B2:B6, A2:A6 = "SAVE20") leaks lowercase rows—and how EXACT locks it down!
EXACT compares text case-sensitively. It is not a binary-file or cryptographic byte-equality test, and cell formatting does not define the comparison. This worksheet preserves spaces and does not perform Unicode normalization.
The helper flags identify rows with the same text and casing as E2. Blank or non-text targets produce no matches. Numeric source codes are excluded; identifiers should be stored consistently as text.
The amount total requires all five amount cells to be numeric. Zero and negative amounts remain valid, while missing amounts and numeric-looking text need correction. The matching-record count is independent of whether amount data is valid.
| Coupon Code (A) | Redemption $ (B) | EXACT vs "SAVE20" (C) | Included in Case-Sensitive Filter? |
|---|---|---|---|
| SAVE20 | $40 | 1 (Exact match) | Included ($40) |
| save20 | $15 | 0 (Lowercase) | Excluded (leaks into SUMIF!) |
| Save20 | $20 | 0 (Title case) | Excluded (leaks into SUMIF!) |
| SAVE20 | $60 | 1 (Exact match) | Included ($40 + $60 = $100) |
| VIP50 | $100 | 0 | Excluded (SUMIF gave $135!) |
Sum case-sensitive filtered rows via EXACT helper flag C2:C6
FILTER's third argument 0 prevents #CALC! if zero rows match the exact casing in E2.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",SUM(FILTER(B2:B6, C2:C6 = 1, 0)))Case-sensitive match count vs case-insensitive SUMIF comparison
SUM(C2:C6) counts matching text records. For the initial plain SAVE20 query only, the case-insensitive SUMIF comparison totals 135. SUMIF additionally interprets wildcard criteria; it is not a universal literal-text comparison.
=SUM(C2:C6)Display the matching amounts
A top-level FILTER spills all matching values. No matching rows display No matches; this is distinct from a matched zero amount.
=FILTER(B2:B6,C2:C6=1,"No matches")Try the example
Use the original values or change them to test your understanding.