FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010,
2007, 2003


December 1, 2016
 
Greetings from The Excel Addict

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'.

I
f 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


Francis Hayes (TheExcelAddict.com)
 

If you missed my last newsletter, you can click here to view it online.


 
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.

This week's 'Excel in Minutes' tip from The Excel Addict

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.

COUNTIF Function With Wildcards in Microsoft Excel 2007 2010 2013 2016 365
Now, are you starting to see ways you can use this technique in your own work?



FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website



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.
"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