How to make COUNTIF case-sensitive in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--EXACT(A2:A8,D1))COUNTIF ignores case, so use EXACT inside SUMPRODUCT: =SUMPRODUCT(--EXACT(A2:A8,D1)).
- Result on the example
- 3 in
D2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF treats upper and lower case as the same, so it counts every spelling of AB-12. EXACT compares each code with D1 character by character, including case, and returns TRUE or FALSE; the double minus turns those into 1 and 0, and SUMPRODUCT adds them up.
=SUMPRODUCT(--EXACT(A2:A8,D1))Result3
A2:A87 rows × 1 col: "AB-12", "ab-12", "AB-12", "Ab-12", …A range: 7 cells in one column.
D1"AB-12"The value in D1.
EXACT(A2:A8,D1){7 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …EXACT: TRUE if two texts match exactly, including case.- text1
A2:A87 rows × 1 col: "AB-12", "ab-12", "AB-12", "Ab-12", … - text2
D1"AB-12"
- text1
-EXACT(A2:A8,D1){7 rows × 1 col} -1, 0, -1, 0, …Makes the value negative.
--EXACT(A2:A8,D1){7 rows × 1 col} 1, 0, 1, 0, …Makes the value negative.
SUMPRODUCT(--EXACT(A2:A8,D1))3SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--EXACT(A2:A8,D1){7 rows × 1 col} 1, 0, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--EXACT(A2:A8,D1)). 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 |
|---|---|---|
| =COUNTIF(A2:A8,D1) | 6Different result | The usual COUNTIF, which ignores case. Counts 6: every spelling of AB-12. |
Common mistakes
- EXACT also checks spaces, so "AB-12 " doesn't match.
- SUMIF, MATCH, VLOOKUP and XLOOKUP ignore case too. Combine them with EXACT the same way.
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.
Make COUNTIF case-sensitive
D2, count the codes in A2:A8 that match D1 exactly, including upper and lower case.Questions
How do I sum case-sensitively?
=SUMPRODUCT(--EXACT(A2:A8,D1),B2:B8), with the amounts in column B.
How do I count only entries in capitals?
=SUMPRODUCT(--EXACT(A2:A8,UPPER(A2:A8))).
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.