How to do a case-sensitive count in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--EXACT(B2:B10,E1))COUNTIF ignores case, so use EXACT inside SUMPRODUCT: =SUMPRODUCT(--EXACT(B2:B10,E1)) counts only cells that match E1 letter for letter.
- Result on the example
- 3 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
EXACT compares two texts and returns TRUE only when every character matches, case included. Given the whole column, it returns one TRUE or FALSE per row; the double minus turns those into 1s and 0s, and SUMPRODUCT adds them up.
Only three orders used "SAVE10" in capitals. COUNTIF would report six, because to it "save10" and "Save10" are the same code.
=SUMPRODUCT(--EXACT(B2:B10,E1))Result3
B2:B109 rows × 1 col: "SAVE10", "save10", "WELCOME", "SAVE10", …A range: 9 cells in one column.
E1"SAVE10"The value in E1.
EXACT(B2:B10,E1){9 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …EXACT: TRUE if two texts match exactly, including case.- text1
B2:B109 rows × 1 col: "SAVE10", "save10", "WELCOME", "SAVE10", … - text2
E1"SAVE10"
- text1
-EXACT(B2:B10,E1){9 rows × 1 col} -1, 0, 0, -1, …Makes the value negative.
--EXACT(B2:B10,E1){9 rows × 1 col} 1, 0, 0, 1, …Makes the value negative.
SUMPRODUCT(--EXACT(B2:B10,E1))3SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--EXACT(B2:B10,E1){9 rows × 1 col} 1, 0, 0, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--EXACT(B2:B10,E1)). We ran it in Google Sheets mode on the same data and it returns 3.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--ISNUMBER(FIND(E1,B2:B10))) | 3 | Case-sensitive "contains": FIND, unlike SEARCH, respects case. Use it when the code can be part of a longer text. |
| =COUNTIF(B2:B10,E1) | 6Different result | The usual count, which ignores case. Counts all six variants of the code. |
Common mistakes
- COUNTIF, COUNTIFS, MATCH and XLOOKUP all ignore case. Only EXACT and FIND tell "SAVE10" from "save10".
- EXACT also compares spaces, so "SAVE10 " with a trailing space does not match. Wrap the range in TRIM if the data is messy.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Do a case-sensitive count
E2, count the orders whose coupon code matches E1 exactly, including upper and lower case.Questions
How do I add another condition to a case-sensitive count?
Multiply the tests: =SUMPRODUCT(EXACT(B2:B10,E1)*(A2:A10<>"")).
Is there a case-sensitive COUNTIF in Google Sheets?
Not directly, but the same SUMPRODUCT and EXACT formula works, and so does =SUMPRODUCT(--REGEXMATCH(B2:B10,"^"&E1&"$")), since REGEXMATCH is case-sensitive.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.