Table of Contents
A case-sensitive COUNTIF formula in Google Sheets allows for counting the number of cells that meet a specific criteria while also taking into consideration the case sensitivity of the data. This means that the formula will only count cells that match the criteria exactly, including any capitalization or spelling variations. To use this formula, the user must specify the criteria and the range of cells to be searched. The formula will then return the total count of cells that meet the criteria. This can be useful in situations where data may vary in case sensitivity, allowing for a more accurate count.
Google Sheets: Use Case Sensitive COUNTIF
By default, the COUNTIF function in Google Sheets is not case-sensitive.
However, you can use the following formula to perform the equivalent of a case-sensitive COUNTIF function:
=SUMPRODUCT(REGEXMATCH(A1:A10,"Mavs"))
This particular formula will only count the number of cells in the range A2:A10 equal to “Mavs” with the exact case match.
In other words, cells with the text “mavs” or “MAVS” would not be counted.
The following example shows how to use this formula in practice.
Example: How to Use Case Sensitive COUNTIF in Google Sheets
Suppose we have the following dataset that contains information about various basketball players:
Suppose we used the following formula to count the number of cells in the Team column that have text equal to “Mavs”:
=COUNTIF(A1:A10,"Mavs")
The following screenshot shows how to use this formula in practice:
The formula returns a value of 4 since it found “Mavs” (case-insensitive) exactly four times in the Team column.
To use a case-sensitive COUNTIF function, we can instead type the following formula into cell D2:
=SUMPRODUCT(REGEXMATCH(A1:A10,"Mavs"))
The following screenshot shows how to use this formula in practice:
This formula returns a value of 2 since only two cells in the Team column have the text “Mavs” with the case matching.
Note: This formula is able to perform a case-sensitive count because the REGEXMATCH function in Google Sheets is case-sensitive by default.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets: