# How to do a case-sensitive SUMIF in Excel

SUMIF ignores case, so test with EXACT inside SUMPRODUCT: `=SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8)`.

**Excel:** `=SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Code | Units |  | Code | KX-10 |
| KX-10 | 120 |  | Units |  |
| kx-10 | 45 |  |  |  |
| KX-12 | 80 |  |  |  |
| KX-10 | 65 |  |  |  |
| Kx-10 | 30 |  |  |  |
| KX-12 | 95 |  |  |  |
| kx-10 | 20 |  |  |  |

Formula in E2: `=SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8)`

Result: 185

## How it works

EXACT compares two texts character by character, case included, and returns TRUE only for a perfect match. Comparing every code with `E1` gives TRUE for the two "KX-10" rows, and SUMPRODUCT adds their units. "kx-10" and "Kx-10" are left out.

## Other ways

- `=SUM(FILTER(B2:B8,EXACT(A2:A8,E1)))`: Excel 365 and Google Sheets: FILTER keeps the exact matches.
- `=SUMIF(A2:A8,E1,B2:B8)`: Plain SUMIF ignores case. (Adds every spelling of kx-10: 280.)

## Common mistakes

- Equals (`A2:A8=E1`) ignores case too, so `=SUMPRODUCT((A2:A8=E1)*B2:B8)` gives the same 280 as SUMIF.
- EXACT also treats extra spaces as differences. TRIM both sides if the data may have them.

## Questions

**How do I make COUNTIF case-sensitive?**

Use the same pattern without the amounts: `=SUMPRODUCT(--EXACT(A2:A8,E1))`.

**How do I do a case-sensitive partial match?**

Use FIND, which is case-sensitive: `=SUMPRODUCT(ISNUMBER(FIND("KX",A2:A8))*B2:B8)`.

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-sumif