How to do a case-sensitive SUMIF in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8)SUMIF ignores case, so test with EXACT inside SUMPRODUCT: =SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8).
- Result on the example
- 185 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 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.
=SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8)Result185
A2:A87 rows × 1 col: "KX-10", "kx-10", "KX-12", "KX-10", …A range: 7 cells in one column.
E1"KX-10"The value in E1.
EXACT(A2:A8,E1){7 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …EXACT: TRUE if two texts match exactly, including case.- text1
A2:A87 rows × 1 col: "KX-10", "kx-10", "KX-12", "KX-10", … - text2
E1"KX-10"
- text1
B2:B87 rows × 1 col: 120, 45, 80, 65, …A range: 7 cells in one column.
EXACT(A2:A8,E1)*B2:B8{7 rows × 1 col} 120, 0, 0, 65, …The * operator multiplies.
SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8)185SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
EXACT(A2:A8,E1)*B2:B8{7 rows × 1 col} 120, 0, 0, 65, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(EXACT(A2:A8,E1)*B2:B8). We ran it in Google Sheets mode on the same data and it returns 185.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(FILTER(B2:B8,EXACT(A2:A8,E1))) | 185 | Excel 365 and Google Sheets: FILTER keeps the exact matches. |
| =SUMIF(A2:A8,E1,B2:B8) | 280Different result | 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.
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 SUMIF
E2, total the units for rows whose code matches E1 exactly, including upper and lower case.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).
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.