Case Sensitive Sumproduct is possible in Google Sheets but not the way you are doing it in Excel. I’ll come to that.
I’ve recently written a series of posts related to Case Sensitive Formulas in Google Sheets. You can use the search field on the top navigation bar with the keyword “Case Sensitive” to fetch links to such posts.
Advanced, Google Doc’s, Sumproduct Function Formulas and Tutorials:
1. How to Use SUMPRODUCT Function in Google Sheets
2. How to Use Date Difference as Criteria in SUMPRODUCT in Google Sheets
3. Compare Sumifs, Sumproduct, Dsum with Example in Google Sheets
4. Chart to Learn Text, Date, Numeric Criteria in Sumproduct Function in Google Sheets
Case Sensitive Sumproduct in Google Sheets
As you know, SUMPRODUCT in Google Sheets is a case In-sensitive function. This function treats “A” and “a” as identical. So how to make Sumproduct Case Sensitive in Google Sheets? Here is our example.
Type the above sample data in a plain Google Doc Spreadsheet keeping the range intact. Now, what I’m going to do is, finding the total amount in Column G for “Product” “SH101A” in Column A.
Note: Please note that here the word “Product” is the field label for Column A, not the Product in the Sumproduct.
See there are similar “products” in Column A, i.e., “SH101A” and “SH101a” but both are different products.
When you apply a normal Sumproduct formula, or you can say case insensitive formula as below, it would return the result 1350.00 as it treats Cell A2, A3 and A6 are as identical.
Now see the Case Sensitive Sumproduct Formula in Google Sheets below which would return the result 900.00. The reason, it finds the match only in cell A2 and A6. The array or range A2:A6 in the above case insensitive formula, got changed here in the case insensitive formula below. Then the text criteria in the first formula “SH101A” find a replacement in the second formula as numeric 1.
Formula Explanation [Step by Step Guide]
The FIND function may not work with an array as below.
So you should use the formula like this.
This formula looks for the array A2:A6 for the key “SH101A” and return 1, if it finds the exact match. Else it would return “#VALUE!”.
When you wrap the above ArrayFormula_Find function combination with an ISNUMBER formula, it would change the above result as below.
This is our replacement of the Array A2:A6. Here numeric value 1 represents the exact match of “SH101A” and 0 represents unmatch. So for this new range, we can use the criteria as numeric value 1 not the text criteria “SH101A”.
This way you can convert a case insensitive Sumproduct formula to Case Sensitive in Google Sheets. But in Excel there is a different approach with the Exact function. Enjoy!