frequency count

Use Excel’s counting functions to build a frequency table for text values.

1. List unique text values
   • Use Advanced Filter to extract unique items from the column that contains the text:
     1. Select the column with the text strings.
     2. On the Data tab, in Sort & Filter, select Advanced.
     3. Choose Copy to another location.
     4. Check Unique records only.
     5. Set Copy to to an empty column and click OK.
   • This gives a list of unique text values to use as categories for the frequency count.
2. Count occurrences of each text value
   • Suppose:
     • Original text data is in A2:A1000.
     • Unique list (from Advanced Filter) is in C2:C20.
   • In D2, enter a COUNTIF formula to count how often the text in C2 appears in the original list:

          =COUNTIF($A$2:$A$1000, C2)
     

   • Fill this formula down alongside all unique values in column C.
   • Column D now contains the frequency count for each text string.
3. Optional: Count only nonblank cells
   • To count how many cells in a range contain any value (text or numbers), use COUNTA:

          =COUNTA(A2:A1000)
     

4. Optional: Count based on multiple conditions
   • If the frequency needs to consider additional criteria (for example, text in column A only when a date in column B is in a range), use COUNTIFS:

          =COUNTIFS($A$2:$A$1000, C2, $B$2:$B$1000, ">=" & $D$1, $B$2:$B$1000, "<=" & $E$1)
     

     where D1 and E1 hold the start and end dates.

This combination of Advanced Filter to get unique values and COUNTIF/COUNTIFS to count occurrences provides a frequency distribution for text strings.

References:

• Ways to count values in a worksheet
• Count unique values among duplicates
• FREQUENCY function
• WorksheetFunction.Frequency method (Excel)
• WorksheetFunction.Frequency(Object, Object) Method