How to Sum if case-sensitive in Excel

Engineering - Follow us to learn every day 💡

❤️ Learning & Engineering

Published Nov 4, 2023

By default, Excel is not case-sensitive in its functions. For instance, when you use SUMIF or SUMIFS, Excel treats "apple" and "Apple" as the same string. However, there are situations where case sensitivity is crucial, such as when product codes, chemical compound notations, or other specific identifiers differ only by letter case. In such scenarios, a case-sensitive summation is necessary.

👉Purchase our book to improve your Excel productivity

Benefits

- Accuracy: Ensures that data is aggregated accurately when case differences are meaningful.

- Data Integrity: Maintains the integrity of data where case variations represent different values or categories.

- Custom Analysis: Enables specialized analysis for datasets where case sensitivity can lead to different interpretations or conclusions.

Article content — How to Sum if case-sensitive in Excel

Step-by-Step:

Setting Up the Spreadsheet

- Column A: "Identifiers" (where the case-sensitive criteria will be applied)

- Column B: "Values to Sum"

Understanding the Logic

We will use an array formula involving the EXACT function, which is case-sensitive, in combination with SUM to perform case-sensitive summation.

👉Purchase our book to improve your Excel productivity

Example

Scenario

You have a list of product codes with associated sales numbers. You want to sum the sales numbers for a specific product code, "ProductA," without confusing it with "producta" or "PRODUCTA."

Sample Data:

- A2: "ProductA"

- A3: "producta"

- A4: "ProductA"

- A5: "PRODUCTA"

- B2: 150

- B3: 200

- B4: 180

- B5: 170

You want to sum the values in column B where the case-sensitive match in column A is "ProductA".

Steps:

1. Input Sample Data:

- Column A: Enter all product codes.

- Column B: Enter the corresponding sales numbers.

2. Case-Sensitive Sum Formula:

- Click on the cell where you want the sum to appear (let's say C2).

- Enter the following array formula (Note: In Excel 365 and Excel 2019, you do not need to press Ctrl+Shift+Enter for array formulas. In older versions, you do):

=SUM((EXACT(A2:A5, "ProductA") * B2:B5))

- Press Enter to get the result.

3. Result:

- C2 will now display the sum of the values for "ProductA" with the exact case, which should be 150 + 180 = 330.

Advanced Tips:

1. Array Formula:

- Remember that in older versions of Excel, you need to confirm array formulas by pressing Ctrl+Shift+Enter instead of just Enter.

2. Dynamic Ranges:

- To make the formula dynamic, you can use INDIRECT or OFFSET to define the range instead of fixed cell references.

3. Combining with IF Function:

- For more complex criteria, combine EXACT with IF within the array formula to check multiple conditions.

4. Data Validation:

- Use data validation to prevent case errors when entering new identifiers.

5. Avoiding Volatility:

- Array formulas can be volatile and may slow down your worksheet. Use them sparingly, and consider using helper columns for larger datasets.

6. Case-Sensitive Lookup:

- If you also need to perform a case-sensitive lookup, consider using INDEX and MATCH with EXACT instead of VLOOKUP.

7. Using VBA:

- For regular use, you might write a VBA function that performs case-sensitive sum to simplify your worksheets and avoid complex array formulas.

Remember to test the formula with a range of possible input scenarios to ensure its robustness. This includes checking cases with no matches, all matches, and some matches to make sure it always returns the correct sum.

👉Purchase our book to improve your Excel productivity :