Text Functions
Case-Sensitive Cell Count & Substring Count (EXACT & FIND)
Count exact uppercase/lowercase matches and case-sensitive substrings where COUNTIF ignores letter case, using row-level --EXACT(A2, $E$2) and --ISNUMBER(FIND($E$3, A2)) helper columns.
Why COUNTIF treats "ID-a" and "ID-A" as identical—and how to separate them!
Both COUNTIF and COUNTIFS are strictly case-insensitive: =COUNTIF(A2:A6, "SKU-a") counts "SKU-a", "SKU-A", and "sku-a" together! That breaks 18-character Base62/Salesforce IDs, chemical element symbols ("Co" cobalt vs "CO" carbon monoxide), and case-sensitive coupon codes!
To enforce strict letter-case matching across every spreadsheet engine, pair a row-level 0/1 helper column =--(EXACT(A2, $E$2)) for exact full-cell equality—or =--(ISNUMBER(FIND($E$3, A2))) for case-sensitive substring matching (unlike SEARCH, FIND distinguishes uppercase from lowercase)—and sum the helper column with SUM(B2:B6)!
| Code (A) | Exact "SKU-a" Helper (B) | Contains "-PRO" Helper (C) | Why It Matches or Fails |
|---|---|---|---|
| SKU-a | 1 | 0 | Exact lowercase "-a" match |
| SKU-A | 0 | 0 | Uppercase "-A" rejected by EXACT! |
| SKU-a-PRO | 0 | 1 | Uppercase "-PRO" matched by FIND |
| SKU-a-pro | 0 | 0 | Lowercase "-pro" rejected by FIND! |
| SKU-a | 1 | 0 | Second exact "SKU-a" (SUM = 2) |
Row-Level Case-Sensitive Exact Match Flag & Total Count
In helper column B2:B6, =--(EXACT(A2, $E$2)) returns 1 only when every character and letter case matches E2; =SUM(B2:B6) totals exact case-sensitive occurrences.
=SUM(B2:B6)Case-Sensitive Substring Check with ISNUMBER(FIND(...))
Because FIND is case-sensitive (whereas SEARCH ignores case), wrapping FIND inside ISNUMBER flags rows containing the exact-case tag in E3 without propagating #VALUE! on non-matches.
=--(ISNUMBER(FIND($E$3, A4)))Try the example
Use the original values or change them to test your understanding.