If you are a frequent user of Microsoft Excel, 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 MS Excel 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.
Table of Contents
1. Count Cells that are Case Sensitive using Formula
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)))
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:
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:
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.
2. Count Cells that are Case Sensitive using VBA
Now, let’s explore our second method—a journey into VBA magic with a user-defined function designed for precise case-sensitive cell counting. Let’s dive into the steps:
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
In the editor, go to Insert > Module to add a new module.
Copy and paste the provided VBA code into the module.
Function CountSubstring(rng As Range, substring As String) As Long
Dim cell As Range
Dim count As Long
count = 0
For Each cell In rng
count = count + (Len(cell.Value) - Len(Replace(cell.Value, substring, ""))) / Len(substring)
Next cell
CountSubstring = count
End Function
Close the VBA editor.
Return to your Excel workbook.
In a cell, type the formula:
=CountSubstring($A$1:$A$6,C1)adjust the range and target text accordingly
Press Enter, and witness the user-defined function counting case-sensitive occurrences.
3. Video: Count Cells that are Case Sensitive
Welcome to this Excel video tutorial, we’re unraveling the intricacies of counting case-sensitive cells. Join us as we delve into two powerful methods—one leveraging the SUMPRODUCT function for a formulaic approach, and the other harnessing VBA magic with a user-defined function.
4. Related Functions
- Excel SUMPRODUCT function
The Excel 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],…)… - Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)… - Excel COUNTIFS function
The Excel COUNTIFS 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]…)… - 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)… - Excel FIND function
The Excel 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])…