How to Do a Case Sensitive SUMIF in Google Sheets

0
178
How to Do a Case Sensitive SUMIF in Google Sheets

It’s possible to do case sensitive SUMIF in Google Sheets! Yes, you heard it right. Many people think that Case Sensitive SUMIF in Google Sheets is not possible. But it’s on the contrary. I will explain you how to do a Case Sensitive SUMIF in Google Sheets. No helper column or virtual helper column require in this case.

Normally SUMIF is a case insensitive function. But with a combination of formulas, you can do a case sensitive SUMIF. In concise, to do a case sensitive SUMIF, you should make use of any case sensitive formula with it. The available case sensitive functions here in Google Sheets are Exact, Find and Query. Both Exact and FIND are text functions. But with SUMIF, we can use FIND function only, not Exact. Query is entirely different, and it can even works like a case sensitive SUMIF. That part, I will explain you at the last part of this tutorial.

How to Do a Case Sensitive SUMIF in Google Sheets – Steps

Sample Data:

Steps: Case Sensitive SUMIF in Google Sheets

Here is a normal SUMIF formula to sum the amount for Product “IL102b”. The formula would obviously return a wrong result.

Normal SUMIF Formula

Formula: =sumif(A2:A5,”IL102b”,G2:G5)
Expected Result: 675.00
Actual Result Returned: 1125.00

Formula Explanation:

In this formula A2:A5 is the range, “IL102b” is the criterion and G2:G5 is the sum range.

Why the formula returns an inaccurate result?

The formula is perfect. But the problem here is, SUMIF treats both “IL102b” and “IL102B” as identical and sums column G accordingly. As already told, SUMIF is a case insensitive function.

Case sensitive SUMIF formula

Here is the case sensitive SUMIF formula. As I already told you, we should use one case sensitive formula together with SUMIF and that function is FIND. Here is that combo.

=sumif(ArrayFormula((find(“IL102b”,A2:A5))),1,G2:G5)
Expected Result: 675.00
Actual Result Returned: 675.00

I’ll explain you how this formula works.

Here I’ve replaced the range A2:A5 in the normal SUMIF formula with the following formula.

ArrayFormula((find(“IL102b”,A2:A5)))

What this formula does?

Google Sheets Find function looks for the exact value “IL102b” in the range A2:A5. When independently use as below, this formula would throw an error. The reason, we can only use a cell reference not range with this formula.

=find(“IL102b”,A2:A5)

So an ArrayFormula combination can work here and the result would be as follows. This replace our original range A2:A5.

Wherever the value “IL102b” in the range A2:A5 appears, the Find ArrayFormula combo returns 1. Here in our case, there is only one match and so 1 appears only one time. Since this is our new range, we can use 1 as criterion instead of “IL102b”. That’s all. As a side note, the same trick is applicable to SUMIFS also.

Now for your information, if you are not very much particular to use Case Sensitive SUMIF formula in Google Sheets, you an use a simple QUERY function alternative as below for our above example.

QUERY Formula Alternative to Case Sensitive SUMIF in Google Sheets

=query(A2:G5,”Select Sum(G) where A=’IL102b’ label Sum(G)””)

Hope you have enjoyed the stay here! Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here