| FREE
Excel
Tips
Newsletter
from TheExcelAddict.com Subscribe here to get more tips like this every week |
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)
Email: fhayes[AT]TheExcelAddict.com
| Quote
of the Day “Three things will not remain long hidden: the sun, the moon, and the truth.” -- Gautama Buddha -- |
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.
| =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.
| FREE
Excel
Tips
Newsletter
from TheExcelAddict.com Subscribe here to get more tips like this every week |
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. |
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630