# How to make COUNTIF case-sensitive in Excel

COUNTIF ignores case, so use EXACT inside SUMPRODUCT: =SUMPRODUCT(--EXACT(A2:A8,D1)).

**Excel:** `=SUMPRODUCT(--EXACT(A2:A8,D1))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Code |  | Code | AB-12 |
| AB-12 |  | Exact count |  |
| ab-12 |  |  |  |
| AB-12 |  |  |  |
| Ab-12 |  |  |  |
| AB-13 |  |  |  |
| AB-12 |  |  |  |
| ab-12 |  |  |  |

Formula in D2: `=SUMPRODUCT(--EXACT(A2:A8,D1))`

Result: 3

## 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.

## Other ways

- `=COUNTIF(A2:A8,D1)`: 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.

## 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)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/case-sensitive-countif