Earlier, we learned the use of case-sensitive SUMIF. Likewise, we can also use case-sensitive COUNTIF in Google Sheets.

For that, no need to use SUMPRODUCT or some other functions. All that you need is a FIND and COUNTIF combination.

As you may already know, the COUNTIF is a case-insensitive function. It treats “Apple” and “apple” as the same.

In other words, it treats uppercase and lowercase letters as the same.

Case-Sensitive COUNTIF in Google Sheets

As usual, let’s start with an example.

In the following example, if COUNTIF is case sensitive, it would have returned the result 3. But it’s not!

Formula # 1:

=countif(A1:A5,"a")

So we need to find a way to make COUNTIF case-sensitive.

With the help of the FIND function in Google Sheets, let’s make the COUNTIF case sensitive.

First, see the formula, then I will explain to you how this COUNTIF_FIND combo works.

Formula # 2:

=countif(ArrayFormula(find("a",A1:A5)),1)

Note:- Formula # 2 may not match the formula on the images below. The above is the correct usage.

How does it (formula # 2) differ from formula # 1?

Here, I’ve replaced the array A1:A5 with the following formula part.

ArrayFormula(find("a",A1:A5))

What does it do?

It converts the array or range A1:A5 as below.

Wherever a case-sensitive match is found, the above formula would return 1.

It is our new COUNTIF range in the formula.

Since our range has only the values 1 and #VALUE errors, we should replace the COUNTIF criteria from “a” to 1.

You might have seen people using a similar range below in case-sensitive COUNTIF formulas.

=ArrayFormula(isnumber(find("a",A1:A5))*1)

It includes an additional ISNUMBER function to convert #VALUE to 0 and 1 or any number returned by the FIND to 1.

Yep! Sometimes the FIND may return a number greater than 1, in case, you are searching for a word in a sentence (partial match).

So the ideal formula for case-sensitive COUNTIF is given below.

Formula # 3: ✓

=countif(ArrayFormula(isnumber(find("a",A1:A5))*1),1)

Bottom Line

One of the problems that you may face with the FIND is a partial match.

For example, it would match “apple” in “pineapple.”

=find("apple","pineapple")

So what is the solution?

You can try REGEX, EXACT, or QUERY whenever you want to convert any case-insensitive formula to case-sensitive.

Here I’ll go with the regex.

You can replace Formula # 3 with this one.

=countif(ArrayFormula(regexmatch(A1:A5,"a")),TRUE)

It also matches partially. To make it an exact match, use the below formula.

=countif(ArrayFormula(regexmatch(A1:A5,"^a$")),TRUE)

That’s all. Thanks for the stay. Enjoy!