We often use **the COUNTIF function** for counting the number of cells based on a specific criterion. However, when we use text criteria in the **COUNTIF** function, it usually isn’t case-sensitive. Hence, if we require a case-sensitive **COUNTIF** operation, we can’t achieve the desired output with the **COUNTIF **function. In this article, I’ll discuss 7 easy ways to execute a case sensitive **COUNTIF** operation in Google Sheets.

**Table of Contents**hide

## A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the discussed examples of how to execute a case sensitive **COUNTIF** operation in Google Sheets.

## 6 Easy Ways to Execute Case Sensitive COUNTIF in Google Sheets

First, let’s get familiar with our dataset. The dataset contains a list of strings that are the same but written in three different cases. Now, let’s count the frequency of each case.

### 1. Combining COUNTIF, ARRAYFORMULA, ISNUMBER, and FIND Functions

First, let’s combine the **COUNTIF**, **ARRAYFORMULA**, **ISNUMBER**, and **FIND** functions to execute a case sensitive **COUNTIF** operation. The **FIND** function can return the position of a string first found within a text value. Since the search key string and text to be searched are the same here, the **FIND** will return position **1** whenever a match is found. Later, the **COUNTIF** function can easily count the number of **1** values in the range.

__Steps:__

- Firstly, select
**Cell E5**. - Afterward, type in the following formula-

`=COUNTIF(ARRAYFORMULA(ISNUMBER(FIND(D5,$B$5:$B$15))*1),1)`

- Then, get the required output by pressing
**Enter**key.

__Formula Breakdown__

**FIND(D5,$B$5:$B$15)**

Firstly, the **FIND** function returns the position of the search key (the content of **Cell D5**) in each cell of the range **B5:B15.** Since the search key string and text to be searched are the same here, the **FIND** will return position **1** whenever a match is found. Else, it will return the **#VAULE** error.

**ISNUMBER(FIND(D5,$B$5:$B$15)**

Here, the **ISNUMBER** function is for converting the **#VALUE** to **0** and any number to **1**.

**ARRAYFORMULA(ISNUMBER(FIND(D5,$B$5:$B$15))*1)**

The **ARRAYFORMULA** function helps the non-array function **ISNUMBER** to deal with and display an array.

**COUNTIF(ARRAYFORMULA(ISNUMBER(FIND(D5,$B$5:$B$15))*1),1)**

Finally, the **COUNTIF** function calculates the number of **1** values in the array displayed by the **ARRAYFORMULA** function.

- At this time, select
**Cell E5**again and hover your mouse pointer above the bottom-right corner of the selected cell.

- The
**Fill Handle**icon will be visible at this point. Use it to copy the formula to other cells of**Column E**.

**Read More: ****[Fixed!] COUNTIF Function Is Not Working in Google Sheets**

### 2. Joining SUMPRODUCT, ISNUMBER, and FIND Functions

We can perform the tasks of the **COUNTIF** and **ARRAYFORMULA** functions using **the SUMPRODUCT function**. It can return the sum of the products of analogous entries in two similar-sized ranges.

__Steps:__

- First, activate
**Cell E5**by double-clicking on it. - Then, insert the following formula-

`=SUMPRODUCT(--ISNUMBER(FIND(D5,$B$5:$B$15,1)))`

- Consequently, press
**Enter**key to get the required result. - Finally, copy the formula to other cells by using the
**Fill Handle**icon.

__Formula Breakdown__

**FIND(D5,$B$5:$B$15,1)**

To start, the **FIND** function provides the position of the search key (the content of **Cell D5**) in each cell of the range **B5:B15**. Since the search key string and text to be searched are the same here, the **FIND** will return position **1** whenever a match is found. Otherwise, it will return the **#VAULE** error.

**ISNUMBER(FIND(D5,$B$5:$B$15,1)**

Afterward, the **ISNUMBER** function converts the **#VALUE** to **0** and any number to **1**.

**SUMPRODUCT(–ISNUMBER(FIND(D5,$B$5:$B$15,1)))**

Consequently, the **SUMPRODUCT** function returns the sum of the products of the two equal-sized arrays.

**Read More: ****Google Sheets Count Cells from Another Workbook with COUNTIF Function**

### 3. Joining COUNTIF, ARRAYFORMULA, and EXACT Functions

One of the limitations of the **FIND** function is that it corresponds to partial matches. For example, although the “car” and “oscar” words are not an exact match, the **FIND** function will return a position in this scenario. Therefore, we’ll use **the EXACT function **as an alternative to the combination of **ISNUMBER** and **FIND** functions. It can return the check whether two text values are equal or not. Now, let’s get started.

__Steps:__

- First, select
**Cell E5**and then type in the following formula-

`=COUNTIF(ARRAYFORMULA(EXACT(D5,$B$5:$B$15)),"True")`

- Afterward, press
**Enter**key to get the required count. - Finally, use the
**Fill Handle**icon to copy the formula to other cells of**Column E**.

__Formula Breakdown__

**EXACT(D5,$B$5:$B$15)**

First, the **EXACT** function checks whether the strings in **Cell D5** and each cell in range **B5:B15** are identical or not. If the strings are identical, the **EXACT** function returns **True**, or else it returns **False**.

**ARRAYFORMULA(EXACT(D5,$B$5:$B$15))**

Here, the **ARRAYFORMULA** function helps the non-array function **EXACT** to deal with and display an array.

**COUNTIF(ARRAYFORMULA(EXACT(D5,$B$5:$B$15)),”True”)**

Finally, the **COUNTIF** function returns the count of **True** entries in the array displayed by the **ARRAYFORMULA** function.

**Read More: ****How to Use COUNTIF for Cells Not Equal to Text in Google Sheets**

### 4. Merging SUMPRODUCT and EXACT Functions

Now, let’s execute the **EXACT** function merged with the **SUMPRODUCT** function. The **SUMPRODUCT** function will substitute the **COUNTIF** and **ARRAYFORMULA** functions here.

__Steps:__

- Active
**Cell E5**by selecting it first and then pressing the**F2**function key. - Afterward, type in the following formula-

`=SUMPRODUCT(--EXACT(D5,$B$5:$B$15))`

- Next, get the required value by pressing the
**Enter**key. - Consequently, use the
**Fill Handle**icon to copy the formula in other cells.

__Formula Breakdown__

**EXACT(D5,$B$5:$B$15)**

To start, the **EXACT** function checks whether the strings in **Cell D5** and each cell in range **B5:B15** are identical or not. If the strings are identical, the **EXACT** function returns **True**, or else it returns **False**.

**SUMPRODUCT(–EXACT(D5,$B$5:$B$15))**

Finally, the **SUMPRODUCT** function returns the sum of the products of the two similar-sized arrays returned by the **EXACT** functions.

**Read More: ****How to Use COUNTIF for True Condition in Google Sheets**

### 5. Uniting COUNTIF, ARRAYFORMULA, and REGEXMATCH Functions

Another alternative to the **FIND** function is **the REGEXMATCH function**. It can also return whether a piece of string matches a regular text value. Here, we’ll combine it with **COUNTIF** and **ARRAYFORMULA** functions. You can join the **SUMPRODUCT** function instead.

__Steps:__

- Firstly, select
**Cell E5**. - Insert the following formula next-

`=COUNTIF(ARRAYFORMULA(REGEXMATCH($B$5:$B$15,D5)),"True")`

- Afterward, get the required output by pressing
**Enter**key. - Finally, use the
**Fill Handle**icon to copy the formula to other cells.

__Formula Breakdown__

**REGEXMATCH($B$5:$B$15,D5)**

First, the **REGEXMATCH** function checks whether the strings in **Cell D5** and each cell in range **B5:B15** are identical or not. If the strings are identical, the **REGEXMATCH** function returns **True**, or else it returns **False**.

**ARRAYFORMULA(REGEXMATCH($B$5:$B$15,D5))**

Here, the **ARRAYFORMULA** function helps the non-array function **REGEXMATCH** to deal with and display an array.

**COUNTIF(ARRAYFORMULA(REGEXMATCH($B$5:$B$15,D5)),”True”)**

Finally, the **COUNTIF** function returns the count of True entries in the array displayed by the **ARRAYFORMULA** function.

**Read More: ****How to Use COUNTIF Function with OR Logic in Google Sheets**

### 6. Combining COUNTIF and QUERY Functions

We can use **the QUERY function** to get a range for an exact match in the **COUNTIF** function. We need to change the dataset to the following for this example.

__Steps:__

- First, select
**Cell G5**and type in the following formula-

`=COUNTIF(QUERY($B$5:$B$15,"SELECT *WHERE B "&$D$5&" '"&F5&"'"),"<>")`

- Press
**Enter**key later to get the required count. - Finally, use the
**Fill handle**icon to copy the formula to other cells of**Column G**.

__Formula Breakdown__

**QUERY($B$5:$B$15,”SELECT *WHERE B “&$D$5&” ‘”&F5&”‘”)**

First, the **QUERY** function runs a **Google Visualization API Query Language** query across the range **B5:B15**. The expression **SELECT * WHERE** starts a query. Next expression **B** indicates the query run in **Column B**. And the expression** “&$D$5&”** indicates the query operator. The **& **symbols are used for extracting the text from **Cell D5**. The expression **‘“&F5&”’** indicates the cell reference of the search term. The apostrophes (**‘’**) symbol differentiates the search term from the search operator.

**COUNTIF(QUERY($B$5:$B$15,”SELECT *WHERE B “&$D$5&” ‘”&F5&”‘”),”<>”)**

Later, the **COUNTIF** function counts the number of non-empty cells returned by the **QUERY **function.

**Read More: ****Use COUNTIF If Cell Contains Specific Text in Google Sheets**

## Things to Be Considered

- One of the limitations of the
**FIND**function is that it corresponds to partial matches. For example, although the “car” and “oscar” words are not an exact match, the**FIND**function will return a position in this scenario.

## Conclusion

This concludes our article to learn how to execute case sensitive **COUNTIF** in Google Sheets. I hope the demonstrated examples were ideal for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website **OfficeWheel.com** for more helpful articles.

## Related Articles

**Google Sheets Add Calculated Field for Pivot Table with COUNTIF****COUNTIF Function with “Not Equal to” Criterion in Google Sheets****Use COUNTIF Function to Count Checkbox in Google Sheets****COUNTIF with Greater than and Less than Criteria in Google Sheets****How to Use VLOOKUP with COUNTIF Function in Google Sheets**