If you want to count cells with specific text then the following methods will be very helpful. This can be applied when you want to count cells with full or partial text. Also, people that analyze very huge worksheets are often counting cells that contain specific text. COUNTIF function can be used when it comes to counting cells containing specific text. This can also be done using special software known as Kutools.
1. Use the COUNTIF function
The COUNTIF functions work together with a wildcard when it comes to counting cells with specific text. A wildcard refers to a special character that allows you to perform fuzzy matching on the text. The count if function is =COUNTIF(B5:B15,"*a*").
How it works
COUNTIF function works by counting cells that meet given criteria in a specific range. For instance, if you want to count the cells containing "a" you should use =COUNTIF(range," a"). But this is limited to the exact text match. So if a cell contains anything more rather than "a" alone then it will not be counted. Since we need to count all the cells that contain a specific then we need to use a wildcard. In this case, we are going to use an asterisk character as our wildcard
The wildcard is important because it can match any number of characters even including zero. This means that it will count all the cells that contain "a" regardless of their position or whether it's followed by other characters to the right or left.
Steps
1. Select a blank cell where the results will be displayed
2. Input the function =COUNTIF(range,"*a*") into the formula bar
3. Press enter and the results will be displayed on the selected empty cell
You can replace the article "a" with whatever text that you want to count on your worksheet.
2. Using Kutools for Excel
Apart from using a formula to calculate you can use a 3rd party tool known as Kutools for Excel. This tool can be very helpful if you want to count and cells with a specified text. You need to download and install Kutools for Excel first so that you can use it.
Steps
1. Download and install Kutools for Excel
2. Click on the Kutools tab and then "select"
3. A drop-down menu will appear then click on select specific cells.
4. A dialogue box will appear allowing you to select the range and your specific text
5. Click okay and the number of cells with the specified text will be populated automatically
Another method that you can use to count cells with any text is by the use of SUMPRODUCT formula combined with the ISTEXT functions. Take a look at the formula structure, SUMPRODUCT(–ISTEXT(range)). It can also be represented by SUMPRODUCT(–ISTEXT(range)). If there are text characters available in a specified range then an array of text returns TRUE otherwise it returns FALSE for other cells. Alternatively, if you want to count the number of cells containing text while excluding strings and spaces then you cause the COUNTIFS function.