Hello Excellers, today lets look at an Excel question that a twitter follower sent to me. Their question was ‘how do I count how many times a certain word was used or appears in data set’?. Well, that is just the question that can be answered using the COUNTIF formula. See my sample data set below. I want to count how many times the word Apple appears.
Right, let’s step through the solution using COUNTIF.
COUNTIF uses a very simple syntax as there are only two arguments.
COUNTIF(range, criteria)The two arguments are:-
range – defines one or several cells to count. You put the range in a formula like you usually do in Excel.
criteria – defines the condition that tells the function which cells to count. This argument can be a number, text string, cell reference or expression.
In this simple example, we want to count how many times the word Apple appears in the data set. So, we can either use the following formula
=COUNTIF($B$3:$D$7,B3) where we use the cell reference C3 as the criteria
or the variation using the text string “Apple”
=COUNTIF($B$3:$D$7,”Apple”) where we directly type in a text string as criteria.
As you can see in the criteria argument we can use a number, text string, cell reference or expression. But, both of the variations result in the same answer.
Note: If you are counting the instances of a text string, then it is case-insensitive. So, if we used the criteria aPPLE it would produce the same outcome.
What Next? Want More Excel Tips?
If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series. Click on the link below
How To Excel At Excel – Formula Friday Blog Posts.
