It’s possible to do case sensitive SUMIF in Google Sheets! Yes, you heard me right. Many people think that Case Sensitive SUMIF in Google Sheets is not possible. But it’s on the contrary.

I will explain to 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. The query is entirely different, and it can even work like a case-sensitive SUMIF. That part, I will try to explain to you in the last part of this tutorial!

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

Sample Data:

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

Normal SUMIF 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 does the formula return 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.

The Case-sensitive SUMIF formula

Here is the case-sensitive SUMIF formula. As I have 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 to 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 does this formula do?

Google Sheets Find function looks for the exact value “IL102b” in the range A2: A5. When independently used 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 replaces 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 the 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 the case-sensitive SUMIF formula in Google Sheets, you can use a simple QUERY function as an alternative as below for our above example range.

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.