How to Do a Case Sensitive Sumproduct in Google Sheets

0
114
How to Do a Case Sensitive Sumproduct in Google Sheets

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.

Sample Data:

Sample Data for Case Sensitive Sumproduct

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.

=SUMPRODUCT((A2:A6=“SH101A”)*(G2:G6))

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.

=sumproduct((ArrayFormula(- -isnumber(find(A2:A6,”SH101A”)))=1)*G2:G6)

In the second formula, I’ve used ArrayFormula, ISNUMBER and FIND functions to create a new array instead of A2:A6. Here is that step by step guide.

Formula Explanation [Step by Step Guide]

The FIND function may not work with an array as below.

=find(A2:A6,”SH101A”)

So you should use the formula like this.

=ArrayFormula(find(A2:A6,”SH101A”))

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!”.

Array Formula, Find Combo for Sumproduct

When you wrap the above ArrayFormula_Find function combination with an ISNUMBER formula, it would change the above result as below.

Formula: =ArrayFormula(–isnumber(find(A2:A6,”SH101A”)))

Result:

Array Formula, ISNUMBER, Find Combo for Sumproduct

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!

LEAVE A REPLY

Please enter your comment!
Please enter your name here