When working with Excel spreadsheets, it is often necessary to count the number of cells that contain specific text. For example, suppose we have a spreadsheet that contains a list of customer orders, and we want to know how many orders were for a particular product. We could manually go through the spreadsheet and count the number of orders for that product, but this would be time-consuming and error-prone. Therefore, we need a way to automate this process using Excel formulas.
Solution
Excel provides several formulas that can be used to count the number of cells that contain specific text. These include COUNTIF, SUMPRODUCT, SEARCH, FIND, and ISNUMBER. In the following sections, we will explain how to use each of these formulas to achieve the desired result.
Using COUNTIF
The COUNTIF formula is used to count the number of cells in a range that meet a certain condition. To count the number of cells that contain specific text, we can use the following syntax:
=COUNTIF(range,"*text*")Where “range” is the range of cells to be searched, and “text” is the specific text to be searched for. The asterisks (*) before and after the text indicate that we are looking for cells that contain the text anywhere in the cell, not just at the beginning or end.
For example, suppose we have a spreadsheet that contains a list of customer orders, and we want to know how many orders were for a particular product called “Widget”. We could use the following formula:
=COUNTIF(A2:A10,"*Widget*")This formula would count the number of cells in the range A2:A10 that contain the word “Widget”.
Using SUMPRODUCT
The SUMPRODUCT formula is another way to count the number of cells that meet a certain condition. To count the number of cells that contain specific text, we can use the following syntax:
=SUMPRODUCT(--(range<>"")*(SEARCH("text",range)>0))Where “range” is the range of cells to be searched, and “text” is the specific text to be searched for. The SEARCH function returns the position of the first character of the text within each cell in the range. If the text is not found, SEARCH returns the #VALUE! error. Therefore, we use the “–” operator to convert the search results into boolean values (TRUE/FALSE) and then multiply them with a second boolean value that checks if the cell is not empty. The resulting array has a value of 1 for each cell that contains the text and is not empty. Finally, we use SUMPRODUCT to add up the values in the resulting array.
For example, suppose we have a spreadsheet that contains a list of customer orders, and we want to know how many orders were for a particular product called “Widget”. We could use the following formula:
=SUMPRODUCT(--(A2:A10<>"")*(SEARCH("Widget",A2:A10)>0))This formula would count the number of cells in the range A2:A10 that contain the word “Widget”.
Using SEARCH and FIND
The SEARCH and FIND functions can also be used to count the number of cells that contain specific text. The difference between these functions is that SEARCH is not case-sensitive, while FIND is case-sensitive.
To count the number of cells that contain specific text using SEARCH, we can use the following syntax:
=SUMPRODUCT(--(ISNUMBER(SEARCH("text",range))))Where “range” is the range of cells to be searched, and “text” is the specific text to be searched for. The SEARCH function returns the position of the first character of the text within each cell in the range. If the text is not found, SEARCH returns the #VALUE! error. Therefore, we use the ISNUMBER function to convert the search results into boolean values (TRUE/FALSE), and then use SUMPRODUCT to add up the values in the resulting array.
For example, suppose we have a spreadsheet that contains a list of customer orders, and we want to know how many orders were for a particular product called “Widget”. We could use the following formula:
=SUMPRODUCT(--(ISNUMBER(SEARCH("Widget",A2:A10))))This formula would count the number of cells in the range A2:A10 that contain the word “Widget”.
To count the number of cells that contain specific text using FIND, we can use the following syntax:
=SUMPRODUCT(--(ISNUMBER(FIND("text",range))))Where “range” is the range of cells to be searched, and “text” is the specific text to be searched for. The FIND function works the same way as SEARCH, but it is case-sensitive.
For example, suppose we have a spreadsheet that contains a list of customer orders, and we want to know how many orders were for a particular product called “Widget”. We could use the following formula:
=SUMPRODUCT(--(ISNUMBER(FIND("Widget",A2:A10))))This formula would count the number of cells in the range A2:A10 that contain the word “Widget”.
Using ISNUMBER
The ISNUMBER function can also be used to count the number of cells that contain specific text. This function returns TRUE if a value is a number and FALSE otherwise. Therefore, we can use ISNUMBER in combination with the SEARCH or FIND function to count the number of cells that contain specific text.
For example, suppose we have a spreadsheet that contains a list of customer orders, and we want to know how many orders were for a particular product called “Widget”. We could use the following formula:
=COUNTIF(A2:A10,"*Widget*")-COUNTIF(A2:A10,"=*Widget*")+SUMPRODUCT(--(ISNUMBER(SEARCH("Widget",A2:A10)))))This formula first counts the number of cells that contain the word “Widget” using the COUNTIF function and wildcard character “”. The COUNTIF function counts the number of cells that match a specified condition. We use the wildcard character “” to search for any cell that contains the word “Widget”, including cells that have additional text before or after the word. However, this would also count cells that contain other words that include “Widget” as a substring. To exclude these cells, we subtract the number of cells that match the condition “=Widget” using another COUNTIF function. This condition matches cells that contain the exact word “Widget” surrounded by any number of characters. Finally, we use the SUMPRODUCT function with the ISNUMBER and SEARCH functions to count the number of cells that contain the word “Widget” using a case-insensitive search.
