Table of Contents
Google Sheets is an incredibly powerful spreadsheet software and one of its most useful features is the use case sensitive COUNTIF. This function allows users to easily count cells that contain a specific text string or value, but with one key difference: it is case sensitive. This means that if a text string appears in both uppercase and lowercase letters, two separate counts will be returned. This is a useful tool for quickly counting and tracking data in a spreadsheet without having to manually search through the data. In addition to counting, the COUNTIF function can also be used to look for specific values and text strings in a range of cells, making it a powerful tool for data analysis and manipulation. This makes it an invaluable tool for businesses and individuals alike who need to track and analyze data quickly and accurately.
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.
The following tutorials explain how to perform other common operations in Google Sheets: