How to Count Frequency of Unique Values in a Column in Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel by Microsoft is a user-friendly program that more than lives up to its stellar reputation. We can do various operations on a dataset thanks to Excel‘s tools and functions. Due to our work with big data sets, we often need to count the frequency of unique and different values in Excel. The calculation of the occurrences of a single value inside a column is broken down and discussed in this article. Therefore, if you want to Count the Frequency of Unique Values in a Column in Excel, use one of these three suitable ways.


Download Practice Workbook

You are cordially invited to obtain a free copy of the sample workbook employed during the presentation.


3 Suitable Ways to Count Frequency of Unique Values in a Column in Excel

For the sake of providing an example, let’s investigate a sample dataset. For instance, the following dataset has two columns titled Player Name and Ballon d’Or Winner. The Player Name column contains names of the world’s greatest footballers of all time, and the other column holds the year in which the players won the Ballon d’Or. We will use each of the three methods to find the distinct names using the UNIQUE function and then determine the Frequency of winning the Ballon d’Or from 2013 to 2022 for each player. In addition, I have yet to say that I have been working with the Microsoft Excel 365 version for this post. Nevertheless, you are free to use any other edition that will provide you with the most significant amount of convenience.

excel count frequency of unique values in a column


1. Utilize COUNTIF Function to Find Frequency of  Unique Values in a Column

The COUNTIF function in Excel can determine how many cells inside a given range satisfy a specific condition. In this context, we will use the COUNTIF function to calculate the occurrences of an item. To finish the assignment, please follow the steps that are listed below.

STEPS:

  • First, create two columns right below the dataset titled Unique Name and Frequency.

Utilize COUNTIF Function to Find Frequency of  Unique Values in a Column

  • Second, select the B16 cell.
  • After that, input the following formula in the Formula bar.

=UNIQUE(B5:B13)

  • Later, hit Enter or Tab key to get the outcome.

Utilize COUNTIF Function to Find Frequency of  Unique Values in a Column

  • Next, pick the C16 and type the below formula in the formula bar.

=COUNTIF(B5:B13,B16)

  • Afterwards, press the Enter or Tab key to see the result.

  • Likewise, use the same procedure in the other cells.
  • To achieve this, utilize the AutoFill Handle icon and drag it up to C19.
  • Consequently, the desired output will be displayed below.

Output of Utilizing COUNTIF Function to Find Frequency of  Unique Values in a Column

Read More: COUNTIFS Unique Values in Excel (4 Easy Ways)


Similar Readings


2. Combine SUM and IF Functions to Calculate Occurences of Distinct Values in Excel

In Excel, the SUM function totals the data you provide. With the IF function, you can compare two numbers in Excel. In this section, we will combine these two functions and build a formula to determine the frequency of unique values in a column. Please adhere to the steps described below to complete the work effectively.

STEPS:

  • First, make two columns, Unique Name and Frequency, right below the dataset.

Combine SUM and IF Functions to Calculate Occurences of Distinct Values in Excel

  • Second, choose cell B16.
  • Then, type the following equation into the formula bar.

=UNIQUE(B5:B13)

  • After that, press the Enter or Tab key to see the result.

  • Next, choose cell C16 and type the formula below into the formula bar.

=SUM(IF($B$5:$B$13=B16,1,0))

  • Hence, press the Enter or Tab key to finish.

  • The other cells, too, use the same procedure.
  • To attain this, use the AutoFill Handle icon and move it up to C19.
  • As a result, the output you want will be shown below.

Output of Combining SUM and IF Functions to Calculate Occurences of Distinct Values in Excel

Formula Breakdown

=SUM(IF($B$5:$B$13=B16,1,0))

To understand this formula, you must know how to use the following Excel functions:

SUM and IF Functions

  • IF($B$5:$B$13=B16,1,0)

The IF function enables us to compare the value of B16 in the range $B$5:$B$16. For, True evaluation, the IF function returns 1. Otherwise, 0. Utilizing the IF function in C16, we get – {1;0;0;0;0;0;0;0;0}

  • SUM(IF($B$5:$B$13=B16,1,0))

Excel’s SUM function totals up the numbers you feed it. In our demonstration, the SUM function will add a set of 0s and 1s provided by the IF function. Employing the SUM functions, we find – 1 for the first iteration.

Read More: How to Count Unique Values Based on Criteria in Another Column in Excel


3. Run Excel VBA Code to Count Frequency of  Single Value in a Column

VBA is an abbreviation for Visual Basic for Applications. Microsoft developed VBA. Using VBA code, we may use Excel‘s incompatible features. In this tutorial, you’ll learn how to use VBA in Excel to display just the unique values and then count the frequency of each. Please complete the assignment following these guidelines.

STEPS:

  • To begin, generate two columns like before named Unique Name and Frequency.

Run Excel VBA Code to Count Frequency of  Single Value in a Column

  • Second, navigate to the Developer tab and click the Visual Basic icon.

Run Excel VBA Code to Count Frequency of  Single Value in a Column

  • After that, click on,

InsertModule

  • Next, input the following code in the Module box.
Sub FrequencyCount()
    Range("B16").Select
    ActiveCell.Formula2R1C1 = "=UNIQUE(R[-11]C:R[-3]C)"
    Range("C16").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R[-11]C[-1]:R[-3]C[-1],RC[-1])"
    Range("C16").Select
    ActiveWindow.SmallScroll Down:=6
    Selection.AutoFill Destination:=Range("C16:C19"), Type:=xlFillDefault
    Range("C16:C19").Select
End Sub
  • Later, click the Run icon or press  F5 .

  • Subsequently, the Macros window will appear.
  • Afterwards, choose the FrequencyCount procedure and tap the Run button.

  • As a result, it will produce the intended output like the below one.

Output of Running Excel VBA Code to Count Frequency of  Single Value in a Column

Read More: Excel VBA: Count Unique Values in a Column (3 Methods)


Conclusion

By following the steps we have just gone through, you will now be able to Count the Frequency of Unique Values in a Column in Excel. Further articles on the ExcelDemy Website are very much like this one. Continue to use them, and let us know if you come up with any new ideas or techniques for finishing the assignment. You are welcome to submit any questions, comments, or suggestions in the below-mentioned area.


Related Articles

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

2 Comments
  1. Two questions in reference to the sub routine:
    Sub FrequencyCount()
    Range(“B16”).Select
    ActiveCell.Formula2R1C1 = “=UNIQUE(R[-11]C:R[-3]C)”
    Range(“C16”).Select
    ActiveCell.FormulaR1C1 = “=COUNTIF(R[-11]C[-1]:R[-3]C[-1],RC[-1])”
    Range(“C16”).Select
    ActiveWindow.SmallScroll Down:=6
    Selection.AutoFill Destination:=Range(“C16:C19”), Type:=xlFillDefault
    Range(“C16:C19”).Select
    End Sub

    It appears to assume there are only four possible unique values (range set to “C16:C19”) and does not take into account the fact that there may be null values. Can you provide more details that would address these concerns? The dataset I work with may have from one to over ten unique values depending on the producer selected. Thanks!

    • Reply Avatar photo
      Lutfor Rahman Shimanto May 14, 2023 at 4:47 PM

      Hello DREW7STER,

      Greetings from our website! Thank you for posting your question on the platform. There is a way to solve your submitted problem of finding the unique values and their frequency. I am providing an Excel VBA code compatible with several unique values, as many as you want to be more specific.

      Excel VBA Code:

      
      Sub FindUniqueValues()
          
          Dim uniqueValues As Variant
          Dim i As Long
          Dim j As Long
          Dim freq As Long
          
          uniqueValues = getUniqueValues(Range("B4:B" & Range("B" & Rows.Count).End(xlUp).Row))
          
          Range("E4:F" & Range("E" & Rows.Count).End(xlUp).Row).ClearContents
          
          For i = LBound(uniqueValues) To UBound(uniqueValues)
              freq = 0
              For j = 4 To Range("B" & Rows.Count).End(xlUp).Row
                  If Range("B" & j).Value = uniqueValues(i) Then
                      freq = freq + 1
                  End If
              Next j
              Range("E" & (i + 4)).Value = uniqueValues(i)
              Range("F" & (i + 4)).Value = freq
          Next i
          
      End Sub
      
      Function getUniqueValues(rng As Range) As Variant
          
          Dim uniqueValues() As Variant
          Dim cellValue As Variant
          Dim i As Long, j As Long
          Dim isUnique As Boolean
          
          ReDim uniqueValues(1 To 1)
          uniqueValues(1) = rng.Cells(1, 1).Value
          
          For i = 2 To rng.Cells.Count
              cellValue = rng.Cells(i, 1).Value
              If Not IsEmpty(cellValue) Then
                  isUnique = True
                  For j = 1 To UBound(uniqueValues)
                      If uniqueValues(j) = cellValue Then
                          isUnique = False
                          Exit For
                      End If
                  Next j
                  
                  If isUnique Then
                      ReDim Preserve uniqueValues(1 To UBound(uniqueValues) + 1)
                      uniqueValues(UBound(uniqueValues)) = cellValue
                  End If
              End If
          Next i
          
          getUniqueValues = uniqueValues
          
      End Function
      

      I hope this will achieve your goal. Feel free to contact us again with any other inquiries or concerns.

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo