How to Count Frequency of Text in Excel

This guide will explain how to count the frequency of text in Excel using three simple and easy ways.

The rules for using the COUNTIF function in Excel are the following:

• The COUNTIF function counts cells in a range that meets a single condition. 
• When we input text strings in the criteria, they must be enclosed in double quotations. However, cell references in the criteria are not enclosed in quotes.
• We can use wildcard characters such as the question mark and the asterisk in the criteria. 
• Since the function does not support an array, we must input a range.
• When we try to match strings longer than 255 characters, the function will return incorrect results.
• When we reference another closed workbook, the function will return a #VALUE error. 

Excel is a fantastic tool to use to perform difficult calculations and complex data analysis. Since it has several built-in functions and tools, we can utilize these features to easily perform complicated tasks.

In this guide, we will be focusing on learning how to count the frequency of text in our data set. Luckily, we can utilize three simple and easy methods to do this task.

Firstly, we can use the COUNTIF function to count the frequency of a certain text in our data set. Secondly, we can also utilize the SUM and IF functions to count the number of times a text appears in our data set. Lastly, we can use the pivot table feature in Excel to count the frequency of text.

Let’s take a sample scenario wherein we need to count the frequency of text in Excel.

Suppose you are conducting an online survey. Before you continue on with your data analysis, you need to verify that there are no duplicated participants in your survey. To do this, you wanted to count the frequency of certain names in your data set to remove duplications. 

So you opted to use the COUNTIF function in a formula to ensure no names appear more than once in your data set. 

Before we move on to the three simple and easy methods to count the frequency of text in Excel, let’s first learn the syntax of the functions we will use.

The Anatomy of the COUNTIF Function

The syntax or the way we write the COUNTIF function is as follows:

=COUNTIF(range, criteria)

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• COUNTIF() is our COUNTIF function. And this function is used to count the number of cells within a range that meet the condition we set.
• range is a required argument. So this refers to the range of cells from which we want to count the non-blank cells.
• criteria is another required argument. And this refers to the condition that can be in the form of a number, expression, or text that will define which cells will be counted.

The Anatomy of the SUM Function

The syntax or the way we write the SUM function is as follows:

=SUM(number1, [number2])

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we start any function in Excel.
• SUM() refers to our SUM function. And this function is used to add all the numbers in a range of cells.
• number1 is the only required argument. So this refers to 1 to 255 numbers we want to sum. However, logical values and text are ignored in cells.
• number2 is an optional argument. And this can act as a supplement to the 1 to 255 numbers we want to sum.

Great! Now we can move on and dive into the three simple and easy methods we can use to count the frequency of text in Excel.

How to Count Frequency of Text in Excel Using COUNTIF Function

Firstly, we can utilize the COUNTIF function to easily count the number of times a certain text appears in our data set. So the COUNTIF function will count the number of cells within the selected range that meet the condition or criteria we give.

In this case, our criteria would be the text we want to count in the data set. For instance, we will count the number of times a survey participant’s name will appear in the data set.

To apply this method, we can simply follow the steps below.

1. Firstly, we will input the text we are looking for in a separate cell. And this will make it easier to input our criteria in the formula. Then, we can type in our formula “=COUNTIF(B2:B10, D2)”. Lastly, we will press the Enter key to return the result.

2. And tada! We have successfully counted the frequency of text in Excel.

How to Count Frequency of Text in Excel Using SUM and IF Functions

Secondly, we can also use a formula combining the SUM and IF functions to count the frequency of a certain text in Excel. So the SUM function will add the cells that meet the criteria or conditions we set using the IF function.

To use this method in your work, we can simply follow the steps below.

1. Firstly, we will create a new place wherein we will input the text we want to find and where we want to place the result. Then, we can type in the formula “=SUM(IF(B2:B10=D2,1,0))”. Lastly, we will press the Enter key to return the result.

2. And tada! We have successfully counted the frequency of text in Excel.

How to Count Frequency of Text in Excel Using Pivot Table

Lastly, we can count the frequency of a text using the pivot table in Excel. Furthermore, we can get the frequency of multiple texts using the pivot table. 

To apply this method to your work, simply follow the steps below.

1. Firstly, we will select the entire data set and go to the Insert tab. Then, we will click PivotTable.

2. Secondly, we will input the range containing our data set. Next, we will choose Existing Worksheet and input a new location to input the results. Lastly, we will click OK to apply the changes.

3. In the PivotTable Fields, we will drag the Participant’s Name to the Rows and Values area.

4. And tada! We have successfully counted the frequency of text in Excel using the pivot table tool.

You can make your own copy of the spreadsheet above using the link attached below. 

And that’s pretty much it! We have successfully explained how to count the frequency of text in Excel using three simple and easy methods. Now you can simply choose any of the methods and apply them to your work whenever you need it.

Are you interested in learning more about what Excel can do? You can now use the COUNTIF function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe