December 1, 2016
Hi fellow Excel Addict,
In today's 'Excel in Minutes' tip I'm going to show you how to 'Count Cells That Include Specific Text'.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you 'The Format Painter Pattern Swipe'. You can read that tip here.
I hope you have a great week and keep on Excelling,
Keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Count Cells That Include Specific Text
The COUNTIF function will count the number of cells in a range that match criteria you specify.
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.
Now, are you starting to see ways you can use this technique in your own work?
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630