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

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. ### 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. ### 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,

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

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.

1. Reply 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 Advanced Excel Exercises with Solutions PDF  