Microsoft Excel is an excellent electronic spreadsheet program developed by Microsoft for various platforms like Windows, iOS, Android and Mac OS. It is a software program which can be used to store, manage and manipulate data all at the same time.
The data in the spreadsheet is stored in tables, which are formed by cells. Microsoft Excel can save multiple worksheets in a single file which is called a workbook. It contains many functions for performing various algebraic and logical calculations. The most complex calculations can be easily done with the help of Excel functions. In this article, we’ll look at how Excel counts cells with specific text.
Let’s consider an example. In the following image of a worksheet, the range is given in column B. Now we have to count the cells with specific text using the COUNTIF function.
The generic formula for counting cells with specific data is
=COUNTIF (range, “*text*”)
The function being used here is COUNTIF. In this generic formula, range means a collection/series/range of cells to be considered and text means the specific text which should be present in the cell. The notation “*” is known as a wildcard matching whichever number of characters are present.
Now, let’s observe the example carefully. In this example, the active cell contains the formula
=COUNTIF (B3: B10, “*m*”)
How Does the Formula Work?
The COUNTIF function counts the number of cells in the given range that contains the pattern “m.” It is done by matching the content of each and every cell with “*m*” pattern. We have supplied this pattern as the criteria. The symbol “*” (asterisk) is called wildcard in excel and “*” means “match any number of the letters.” Therefore, the pattern will count the cell which contains “m” at any position. The count of cell matching the pattern is returned in the form of a number.
We can adjust the formula with ease to utilize the contents of another cell for the criteria. For example, if cell C2 has text matching the criteria provided by us, then we can use the formula as:
=COUNTIF (range, “*”&c2&”*”)
The version of the formula used in the above example is not a case-sensitive version. You can also count the cells with specific text while considering the uppercase or lowercase status of the characters as well.
Using ISNUMBER and SEARCH Functions
We can make the use of ISNUMBER and SEARCH functions to count the cells which contain the specific text. The generic formula you can use is
=ISNUMBER (SEARCH (substring, text))
The search function is used along with the ISNUMBER function to ultimately check if the cell contains the specific text. Here, the substring is the text you are searching for, while the text is the matter present in that particular cell.
It is very important to note here that this formula can only tell you if the text in the given range contains the specified substring or not—and not the count of the cell. So how can we get the count of the cells? Let’s consider another example.
In the given example, the formula inactive cell is:
=ISNUMBER (SEARCH (C3, B3))
The formula returns a TRUE value if it finds the mentioned substring and FALSE value if it does not find it. So, by counting all the TRUE values, you can get the number of cells containing the specific text.
How Does the Formula Work?
The Search function returns the position of the string where it is found in the text and ‘#VALUE! error’ if the string is not found. This fact is used to test if the string is found, by using ISNUMBER function. The function ISNUMBER returns TRUE for numeric values and FALSE for anything else other than a number. Therefore, when the SEARCH function finds the string, it returns its position as a number and ISNUMBER returns a TRUE value. When SEARCH does not find the string, it returns the #VALUE! error, which is not a numeric value; hence ISNUMBER returns the FALSE value.
If you want to have a case-sensitive version, then you cannot use COUNTIF. Here, you need to make use of the formula based on the FIND function or ISNUMBER function to test every individual cell in the given range.
The generic formula is: =ISNUMBER (FIND (substring, text))
The FIND function is case-sensitive. You have to provide the range of cells to be tested and then use the SUMPRODUCT function to count the result. The formula can be given as:
=SUMPRODUCT (–(ISNUMBER (FIND (text, range)))
Here, “text” means the text you are searching for, and “range” is the range of the cells to be tested. In this formula, there is no need to make use of the wildcard because the FIND function returns a number if it finds text anywhere in the cell.
So these are the functions through which Excel counts cells with specific text. Let’s have a look at few more function-related formulas apart from those used in above examples.
Running Count of Occurrence in the Given Range
To get a running count of specific values appearing in the given range of cells, the COUNTIF function can be used. It is used with “mixed reference” for getting a running aggregate value. Let’s consider an example.
In the given example, the COUNTIF function is nested within IF function to find out the number of occurrences of “Yellow.” The formula reads as: =IF(D4=”Yellow” COUNTIF($D$4: D4, “Yellow”),””)
Use of COUNTIF for Summary Count
Whenever we work with a numeric data, there can be a general requirement for doing summary calculations, and depicting the aggregate in various manners. These calculations can include total counting based on category, color, sizes, etc. Using the COUNTIF function is the easiest way for generating these types of totals.
The generic formula is:
COUNTIF (range, criteria).
Here, “range” refers to the range of cells to be considered and “criteria” refers to the criteria to be tested.
The above example is for an order for trousers. The summary data is created for the count of different sizes. The formula used here is
=COUNTIF(D4:D10, F6). This returns the number of times the sizes appeared in the given range.
Counting Either One Value or Another
Apart from this, you can use the SUMPRODUCT and ISNUMBER functions together for counting the cells which contain either one value or another. You can make use of a helper column and then measure the count, or you can make use of a complex single-cell formula. But while using these types of formulas you need to be careful about not counting the double values.
If a Cell Contains Multiple Values
If the cell contains more than one thing and gives away a standard result for the first round of matching values, you can make use of INDEX or MATCH formula depending upon the SEARCH function.
So, these are a few formulas used for Excel’s counting of cells with specific text. Let’s now have a quick summary of the functions related to this operation.
COUNTIF is an Excel function to count the number of cells which meet up with the given single criteria. It can be used to count cells containing data like numbers, dates, and characters matching the given specific criteria. The logical operator supported by the COUNTIF function is ‘>’.
The SUMPRODUCT function of Microsoft Excel multiplies the given ranges together and gives the sum of the multiplication as a result. You may find this task tedious but in reality, SUMPRODUCT is an immensely versatile function. It can be utilized to do counting and calculate sums like the other functions (e.g., COUNTIF and SUMIF), but in a more detailed way.
Microsoft Excel’s FIND function is also a very useful function for finding out the position of a given text. The function returns the position of the given text in the form of a number and if it does not find that text, it returns the #VALUE! error. It can be used by combining with the ISNUMBER function in Excel to count cells with specific text.
The ISNUMBER function of Microsoft Excel gives results in TRUE or FALSE values, and is used to find out whether the cell contains a numeric value or not. When a cell contains a number, it gives TRUE value and if it does not contain a number, the function gives the FALSE value.
So, now that you have learned about how to count cells with specific texts in Excel and the details about the formulas and functions related to this operation, you can easily do these kinds of operations. Go ahead and use these formulas and functions effectively to make your calculations easy.