Math
Case-sensitive sum and count with EXACT helper flags
Prevent SUMIF and COUNTIF from merging case-distinct IDs like "001XyzAB" and "001Xyzab" by pairing EXACT with SUMPRODUCT.
Use exact case when it is part of an identifier
SUMIF and COUNTIF normally match text without distinguishing letter case. When your identifier contract is case-sensitive, EXACT helper flags keep otherwise similar identifiers separate. The short IDs here are fictional examples, not validated Salesforce IDs. Their 90,000 combined total is normal case-insensitive matching behavior, not a SUMIF defect.
C2 = --EXACT(A2,$E$2), filled down, compares text including letter case and whitespace. It does not trim or normalize IDs. Keep identifiers as text to preserve leading zeros; require a nonempty target and numeric amounts before aggregation.
| CRM Account ID (A) | ARR $ (B) | EXACT vs "001XyzAB" (C) | Case-insensitive vs exact-case sum |
|---|---|---|---|
| "001XyzAB" (Rows 2 & 4) | $12,000 + $18,000 | 1 (True Match) | Exact Sum = $30,000 (2 rows) |
| "001Xyzab" (Rows 3 & 6) | $45,000 + $15,000 | 0 (Different case!) | Case-insensitive sum includes both variants: $90,000 |
| "001XyzCD" (Row 5) | $9,000 | 0 | Excluded by both |
Case-sensitive conditional sum
Multiplies the exact-case flag column C2:C6 by ARR B2:B6.
=IF(OR(COUNTBLANK(E2)>0,COUNT(B2:B6)<>5),"Invalid inputs",SUMPRODUCT(C2:C6, B2:B6))Count exact-case matches
Summing C2:C6 gives the exact case-sensitive count (2 vs. COUNTIF's 4).
=IF(OR(COUNTBLANK(E2)>0,COUNT(B2:B6)<>5),"Invalid inputs",SUM(C2:C6))Try the example
Use the original values or change them to test your understanding.