Count Cells that are Case Sensitive in Google Sheets

If you are a frequent user of Google Spreadsheets, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out that it is the most inefficient way to do it, particularly when Google Spreadsheets has the versatility to do it in a matter of seconds. As a result of attentively reading this article, you will learn about the many adding or counting case-sensitive cells.

counting the cells that are case sensitive in google sheets1

Simple generic formula:

=SUMPRODUCT(--ISNUMBER(FIND(value, range)))

Summary

You may use a formula depending on the ISNUMBER and FIND functions and the SUMPRODUCT function to calculate the number of cells that contain specified text (i.e., hold a substring), taking into consideration upper and lower case. D1 in the example displayed has the following formula, which has been written down:

=SUMPRODUCT(--ISNUMBER(FIND(C1,$A$1:$A$6)))

counting the cells that are case sensitive in google sheets1

Let’s See How This Formula Works

In this example, the purpose is to count the number of times each code appears as a substring in a case-sensitive manner, as shown below. When calculating text values, the functions COUNTIF and COUNTIFS are excellent choices. However, since these functions are not case-sensitive, they cannot be utilized to address this issue. To check for substrings, the FIND function should be used in conjunction with the ISNUMBER function, and the SUMPRODUCT function should be used to sum up, the results.

= FIND(C1,$A$1:$A$6)

The FIND function is case-sensitive and accepts three parameters: find text, within the text, and start num. It is also case-sensitive and takes three arguments. Here is the text we are searching for, and this is the text that we are searching inside. Start num is the number of characters to begin searching in find text. Because start num defaults to 1, we are not supplying a number in this instance. This is necessary since we always want FIND to begin with the first character. When finding the text is discovered inside within text, FIND returns the position of the found text as a number: When finding the text is located within text, FIND returns the position of the found text as a number:

counting the cells that are case sensitive in google sheets1

Note: When the find text function fails to yield a result, FIND returns the #VALUE! Error.

=ISNUMBER(FIND(C1,$A$1:$A$6))

This implies that we may be using the ISNUMBER function to transform the output of the FIND function into a TRUE or FALSE value using the ISNUMBER function. A TRUE value will arise from any number, while a FALSE value will result from any error:

counting the cells that are case sensitive in google sheets1

As demonstrated in the example, we have four substrings in column C and a variety of codes in columns A1:A6, representing the data for the designated range. Specifically, we want to know how many times each substring in C1:C4 occurs in A1:A6, and we want to know how many times each substring is case-sensitive.

Related Functions

  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Google Sheets FIND function
    The Google Sheets FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Google Sheets SUMPRODUCT function
    The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Google Sheets COUNTIF function
    The Google Sheets CHOOSE function returns a value from a list of values based on index. The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…) …
  • Google Sheets COUNTIFS function
    The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
Related Posts

Calculating Average Of The Numbers in Google Sheets
_3 Average numbers in google sheets1

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets
Average the last 3_ 5 or N numeric values in google sheets1

As an Google sheets user, you might have come across a task in which you need to calculate the average values of the last 2 numeric values, and you might have done this task manually but suppose if the last ...

Add Workdays in Google Sheets
Add workdays no weekends in google sheets1

To add or subtract workdays days from a date, respecting holidays but assuming a 7-day week: you can use the WORKDAY.INTL function with an argument that tells it how many days should be subtracted/added as well as which specific holidays ...

Add Row Numbers And Skip Blanks in Google Sheets
Add Row Numbers And Skip Blanks in google sheets1

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have ...

Add Months To Date In Google Sheets
Add Months To Date in google sheets1

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months ...

Add Leading Zeros in Google Sheets
Add Leading Zeros To Numbers in google sheets1

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

Add Minutes to Time in Google Sheets
Add Minutes to Time in google sheets1

Adding whole hours or decimal minutes to a given time in google sheets is a fairly simple process. You can do this by using the Start_time+TIME(0,minutes,0) and Start_time+minutes/1440 functions. In this add minutes to time guide, we will show you ...

Add Hours to Time in Google Sheets
Add hours to time in google sheets1

Adding whole hours or decimal hours to a given time in Google Sheets is a fairly simple process. You can do this by using the Start_time+TIME(hours,0,0) and Start_time+hours/24 functions. In this add hours to time guide, we will show you ...

Average Last N Values in Google Sheets
Average last 5 values in google sheets1

Average is a valuable function in google sheets that allows you to rapidly compute the average of the past N values in a column. However, you may need to insert new numbers beneath your original information from time to time, ...

Assigning Points based on Late Time in Google Sheets
assign points based on late time in google sheets1

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed. If you intend to pursue along this guide, you may do so by downloading ...

Sidebar