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.
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.
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.
Here what is the difference with the first formula. I’ve replaced the array A1:A5 with the following formula part here.
What it does?
It converts the array or range A1:A5 as below.
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.
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.