Count Cells That Include Specific Text
by Francis Hayes (The Excel Addict)
The COUNTIF function will count the number of cells in a range that match criteria you specify.
| =COUNTIF(range,
criteria) range - is the range from which you want to count cells. criteria - can be text, a number, an expression, or a cell reference to be used to define which cells will be counted. For example, the formula =COUNTIF(B:B, "dog") will count the number of cells in column B with the word dog. |
However, if you ever need to count the number of cells in a range of cells that include a specific word or string of characters anywhere within the cell (not necessarily matching the entire cell), the trick is to surround your criteria with asterisks (*).
The asterisk is a wildcard that represents any number of characters.
The formula =COUNTIF(B:B,"dog") will count the number of cells in column B that have the word 'dog'.
By adding asterisks to the beginning and end of your criteria string, for example, =COUNTIF(B:B,"*dog*"), this will count all cells that 'include' the word 'dog' anywhere within the cell, such as 'dog jackets' or 'reversible dog coats'.
To make this function even more flexible, use a cell reference for your criteria. When you do that, you can't simply type asterisks before and after your criteria cell reference. To add asterisks before and after the cell reference, you will need to type the asterisk surrounded by double quotes and use ampersands (&) to concatenate them with the cell reference.
For example, if D1 is your criteria cell and contains the word 'dog', you can rewrite your formula to be =COUNTIF(B:B,"*" & D1 & "*") which is equivalent to =COUNTIF(B:B,"*dog*").
Now you can simply change the value in cell D1 to count cells based on new criteria or use multiple cells for your criteria and copy your COUNTIF formula to reference these cells.
If cell D1 contains 'dog' and cell D2 contains 'cat' you can enter =COUNTIF(B:B,"*" & D1 & "*") in cell E1 and copy it down to cell E2 to give you counts for both your criterion.
If you found this tip helpful, please share it with your friends and colleagues.
To get more tips every week like this one...
'Spreadsheet Tips From An Excel Addict'
'Excel in Seconds' & 'Excel in Minutes'
Plus you also get my 'Excel in Seconds' E-book as a BONUS!
(Download it immediately after you sign up)
Copyright Francis Hayes © All Rights Reserved
8 Lexington Place, Conception Bay South, NL Canada A1X 6A2
Phone 709-834-4630
This site is not affiliated with Microsoft Corporation.