How to Perform a Case Sensitive COUNTIF in Google Sheets

0
140
Perform a Case Sensitive COUNTIF in Google Sheets

Earlier we have learned the use of Case Sensitive SUMIF. Likewise, we can also use Case Sensitive COUNTIF in Google Sheets. No need to look around QUERY or SUMPRODUCT to do this. All you need is a FIND and COUNTIF combination.

Needless to say Countif is a Case Insensitive Function. It treats “Apple” as well as “apple” as identical.

Must Read: Learn Google Sheets All Count Functions at One Place

If you are not familiar with COUNT functions, then please check my above tutorial first.

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. So we need to find a way to make COUNTIF case sensitive.

case sensitive countif example 1

With the help of FIND function in Google Sheets, we are going to make COUNTIF case sensitive. First see the formula, then I will explain you how this COUNTIF_FIND combo works.

Case sensitive Countif formula in Google Sheets

Here what is the difference with the first formula. I’ve replaced the array A1:A5 with the following formula part here.

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

What it does?

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

Replace the range in Countif with FIND formula

Wherever exact match is found, the above formula would return 1. This is our new COUNTIF range in the formula. Since our range has now new lookup values 1 and ‘#VALUE!’, we should replace the COUNTIF criteria from “a” to 1.

You might have seen people using the Case Sensitive Countif formula as below. It also works, but not necessary. It includes an additional ISNUMBER function to replace ‘VALUE!’ with either 0 or FALSE. We are using 1 as criteria. So it’s irrelevant here.

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

Bottom Line:

Whenever you want to make any case insensitive formula to case sensitive, the available options in Google Sheets are EXACT and FIND functions. You can use any of these functions in combination with the case in-sensitive formulas. If nothing works, a proper use of QUERY formula alone can solve your puzzle.

LEAVE A REPLY

Please enter your comment!
Please enter your name here