Sum if Case Sensitive? Excel & Sheets Guide

Yes, you can sum values based on case-sensitive criteria in Excel and Google Sheets, but the standard SUMIF function cannot do it. You need to use SUMPRODUCT with the EXACT function in Excel, or FILTER with EXACT in Google Sheets. This page explains how to build both formulas, when to use each, and how to avoid common mistakes.

Formula

For Excel, the formula is:

SUMPRODUCT(EXACT(range, criteria) * sum_range)

For Google Sheets, the formula is:

SUM(FILTER(sum_range, EXACT(range, criteria)))

Where:

• range – the cells containing text you want to check (e.g., A1:A5).
• criteria – the exact text (including case) you want to match (e.g., “apple”).
• sum_range – the cells with numbers to sum (e.g., B1:B5).

EXACT returns TRUE only if two text strings are identical, including letter case. In Excel, multiplying TRUE/FALSE by numbers converts them to 1/0, so SUMPRODUCT sums only the values where the condition is TRUE. In Google Sheets, FILTER returns only the rows where EXACT is TRUE, then SUM adds them.

Worked Example

Using the default values: range = A1:A5, sum range = B1:B5, criteria = “apple”. Suppose the data is:

• A1: apple, B1: 10
• A2: Apple, B2: 20
• A3: APPLE, B3: 30
• A4: apple, B4: 40
• A5: pear, B5: 50

In Excel, the formula evaluates EXACT(A1:A5, “apple”) to {TRUE; FALSE; FALSE; TRUE; FALSE}. Multiplying by B1:B5 gives {10; 0; 0; 40; 0}. SUMPRODUCT sums these to 50.

In Google Sheets, FILTER(B1:B5, EXACT(A1:A5, “apple”)) returns {10; 40}, and SUM gives 50. Both give the same result: 50, which is the sum of only the lowercase “apple” rows.

Why Standard SUMIF Fails

SUMIF and SUMIFS are not case-sensitive. They treat “apple”, “Apple”, and “APPLE” as the same text. This is by design – most spreadsheet comparisons ignore case. If you need to distinguish between them, you must use EXACT, which is the only built-in function that compares case exactly.

For example, SUMIF(A1:A5, “apple”, B1:B5) would return 100 (10+20+30+40) because it matches all three case variants. This is a common source of errors when data contains product codes, usernames, or file names where case matters.

How to Use the Formulas

To use the case-sensitive sum in your own sheet:

1. Identify the range that contains the text you want to check (e.g., product codes in A2:A100).
2. Identify the range with the numbers to sum (e.g., quantities in B2:B100).
3. Decide the exact text (including case) you want to match. Put it in a cell or directly in the formula.
4. In Excel, enter: =SUMPRODUCT(EXACT(A2:A100, D1) * B2:B100) where D1 contains the criteria.
5. In Google Sheets, enter: =SUM(FILTER(B2:B100, EXACT(A2:A100, D1))).

Both formulas are array formulas, but in modern Excel and Google Sheets you don’t need to press Ctrl+Shift+Enter; they work normally.

Common Mistakes and Limitations

• Using SUMIF with wildcards: SUMIF supports wildcards (* and ?), but EXACT does not. If you need wildcards, you cannot use this method directly.
• Mixing criteria types: EXACT works with text and numbers, but if your criteria is a number, it may not behave as expected. For numbers, use a simple equality check instead.
• Trailing spaces: EXACT considers spaces significant. “apple” and “apple ” are different. Use TRIM to clean data first.
• Array size mismatch: In Excel, the range and sum_range must be the same size; otherwise SUMPRODUCT returns an error.
• Google Sheets FILTER with empty results: If no rows match, FILTER returns an error. Wrap it in IFERROR to show 0.

Alternatives and Related Formulas

If you need to sum based on case but also have multiple conditions, you can extend the Excel formula: =SUMPRODUCT((EXACT(range1, criteria1)) * (range2 = criteria2) * sum_range). In Google Sheets, you can use FILTER with multiple EXACT conditions: =SUM(FILTER(sum_range, EXACT(range1, criteria1), range2 = criteria2)).

If you need to count case-sensitive matches instead of summing, use =SUMPRODUCT(--EXACT(range, criteria)) in Excel, or =COUNTIF(range, criteria) (but COUNTIF is not case-sensitive, so use =SUMPRODUCT(--EXACT(...))). In Google Sheets, use =COUNTIF(range, criteria) is not case-sensitive, so use =SUMPRODUCT(--EXACT(range, criteria)) or =COUNTIFS(range, criteria) (also not case-sensitive). For counting, the same EXACT approach works.

Frequently Asked Questions

Does SUMIF work with case-sensitive in Excel?

No, SUMIF and SUMIFS are not case-sensitive. They treat text as case-insensitive. To sum with case sensitivity, you must use SUMPRODUCT with EXACT in Excel, or FILTER with EXACT in Google Sheets.

Can I use wildcards with EXACT?

No, EXACT does not support wildcards. It only checks for exact equality, including case. If you need wildcards, you need a different approach, such as using a helper column with SEARCH (which is case-insensitive) or using VBA (in Excel) or custom functions.

Why does my formula return 0?

This usually means no exact match was found. Check for extra spaces, different case, or non-printing characters. Use TRIM and CLEAN to remove them. Also verify that the range and sum_range are the same size.

Is there a case-sensitive SUMIF in Google Sheets?

No, SUMIF in Google Sheets is also case-insensitive. The recommended alternative is SUM with FILTER and EXACT, as shown above. You can also use QUERY with the “matches” clause, but that requires a slightly different syntax.

Can I use this with dates or numbers?

EXACT works with numbers and dates, but since numbers and dates don’t have case, it’s usually unnecessary. For numbers, a simple equality check (e.g., A1=5) works fine. For dates, use the DATE function or a cell reference.

Related

• Mass Effect Crossover: Platform & Edition Guide
• Samsung Galaxy S21 Press Renders Leak?
• Affordable Sports Car: Porsche Handling, Lexus Reliability

sel platform | Platform | Excel=0, Google Sheets=1
in r | Range with text | - | A1:A5 | 0
in s | Sum range | - | B1:B5 | 0
in c | Criteria (text) | - | apple | 0
out result | Case-sensitive sum | - | IF(platform=0, SUMPRODUCT(EXACT(r, c) * s), SUM(FILTER(s, EXACT(r, c)))) | 2