How to Calculate Text Frequency in Excel

Using the COUNTIFS Function

The COUNTIFS function is a function similar to the COUNTIF function but can be used to count the number of cells in one or more ranges that meet one or more criteria. This function is very useful if we want to calculate the frequency of text with more complex or specific conditions.

The syntax of the COUNTIFS function is as follows:

 =COUNTIFS(range1, criteria1, [range2], [criteria2], …)

Where:

•  range1 is the first range of cells that we want to calculate the frequency of
•  criteria1 is the first criterion that cells in the first range must meet
•  range2 is the second cell range that we want to calculate the frequency for (optional)
•  criteria2 is the second criterion that cells in the second range must meet (optional)
•  … is an additional range of cells and criteria that we can add as we need (optional)

For example, suppose we have the same data as before:

This data is stored in cells A1:C9. If we want to count the number of males (L) over 25 years old in this data, we can use the following formula:

 =COUNTIFS(B2:B9,”L”,C2:C9,”>25″)

This formula will return the value 3 because there are three cells in the range B2:B9 that contain the text “L” and also meet the criterion that those cells must be in the range C2:C9 that contain numbers greater than 25. We can write this formula in cell E6.

If we want to count the number of women (P) between the ages of 23 and 26 in this data, we can use the following formula:

 =COUNTIFS(B2:B9,”P”,C2:C9,”>=23″,C2:C9,”<=26″)

This formula will return the value 3 as well, because there are three cells in the range B2:B9 that contain the text “P” and also meet the criterion that those cells must be in the range C2:C9 that contain numbers greater than or equal to 23 and smaller or equal to 26. We can write this formula in cell E7.

By using the COUNTIFS function, we can calculate text frequency in Excel more flexibly and accurately. However, this function still requires us to write the formula manually and adjust the range and criteria as we want. If we want to calculate text frequency in Excel more easily and quickly, without the need to write formulas, we can use a pivot table.

Using Pivot Tables

A pivot table is a very useful feature in Excel to perform data analysis quickly and easily. Pivot tables allow us to rearrange, group, and summarize data from a table or range of data into a new, more informative, and concise tabular form. By using a pivot table, we can count text frequency in Excel with several clicks only.

To use a pivot table, we need to do as following steps:

• Select all the data we want to analyze, including the column headings.
• Click the Insert tab on the ribbon, and then click PivotTable in the Tables group.
• In the Create PivotTable dialog box, select the location where we want to put the pivot table, for example, in a new worksheet or the same worksheet.
• Click OK to create a pivot table.
• On the PivotTable Fields pane, select the column we want to calculate the frequency for, for example, the Gender column, and then drag it to the Rows area.
• On the PivotTable Fields pane, select the same column again, such as the Gender column, and then drag it to the Values area.
• In the Values area, right-click on the column name that appears, for example, Sum of Gender, and then select Value Field Settings.
• In the Value Field Settings dialog box, select the Count option on the Summarize value field by list.
• Click OK to apply the settings.

By doing the above steps, we will get a pivot table displaying the text frequency of our selected column. For example, if we use the same data as before, we would get a pivot table like this:

This pivot table shows that there are four males and four females in our data. We can add another column to the Rows or Columns area to create a more detailed pivot table. For example, if we add an Age column to the Columns area, we will get a pivot table like this:

This pivot table shows that there is one person who is 22 years old, one person who is 23 years old, and so on. We can also look at the distribution of sexes by age. For example, out of four people over four over 25 years old, three of them are men and one of them is women.

By using a pivot table, we can calculate text frequency in Excel very easily and quickly, without the need to write formulas. We can also customize the appearance and settings of the pivot table according to our needs. However, pivot tables may not be suitable for all types of data or analysis. If we want to calculate text frequency in Excel more sophisticatedly and professionally, we can use data analysis tools.