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.

Table of Contents hide

Download Practice Workbook

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

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

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

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

Conclusion

Download Practice Workbook

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

Count Frequency of Unique Values.xlsm

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.

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.

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.

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.

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.

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.

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.

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.

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

After that, click on,

Insert → Module

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.

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.

2 Comments

Drew7ster May 14, 2023 at 6:24 AM

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
  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

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

Download Practice Workbook

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

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

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

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

Conclusion

Related Articles

Lutfor Rahman Shimanto

How to Make a Schedule for Employees in Excel (3 Types)

How to Count Number of Columns in Excel (5 Suitable Examples)

How to Make an Availability Schedule in Excel (with Easy Steps)

Excel Reference Named Range in Another Sheet (3 Examples)

Leave a reply Cancel reply