How to Count Frequency of Text in Excel


You can use the COUNTIF(range, criteria) function to count how often specific text occurs in an Excel column.

The following examples show how to use this function in practice.

Example 1: Count Frequency of One Specific Text

Suppose we have the following column in Excel that shows various NBA team names:

If we’d like to count how often the team name “Hornets” appears in the column, we can use the following formula:

=COUNTIF(A2:A21, "Hornets")

The following screenshot shows how to use this formula in practice:

We can see that “Hornets” appears 3 times.

Example 2: Count Frequency of Multiple Text

If we’d like to count the frequency of multiple different text, we can use the UNIQUE() function to get an array of every unique text that appears in a column:

We can then use the COUNTIF() function to count how frequently each team name occurs:

We can see that:

  • The team name “Mavericks” occurs 3 times.
  • The team name “Spurs” occurs 2 times.
  • The team name “Lakers” occurs 3 times.

And so on.

Additional Resources

The following tutorials offer additional information on how to count frequencies in Excel:

How to Create a Frequency Distribution in Excel
How to Calculate Relative Frequency in Excel
How to Calculate Cumulative Frequency in Excel

Featured Posts

One Reply to “How to Count Frequency of Text in Excel”

  1. How do you do this if the phrases are all different?
    Various phrases but you are looking for the frequency of appearing terms within those phrases in excel – 5000 phrases…
    for example
    light bulb
    home appliances
    lightbulb for home
    bathroom bulbs
    bathroom sink
    how to unscrew a lightbulb safely
    etc.
    how would you find the frequency of popular phrases within the excel sheet? Only finding information on single words not multiple variables.

Leave a Reply

Your email address will not be published. Required fields are marked *