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

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.

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

**Similar Readings**

**How to Use COUNTIF for Unique Text (8 Easiest Ways)****Count Unique Values Using Excel Pivot Table (3 Ways)****How to Count Unique Names in Excel (6 Simple Methods)****Excel SUMPRODUCT Function to Count Unique Values with Criteria****How to Count Unique Values Using Excel Formula (4 Easy Ways)**

### 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 **0**s and **1**s 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**.

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

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

**How to Count Unique Values in Excel with Multiple Criteria****Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples)****How to Count Unique Values in Filtered Column in Excel (5 Methods)****Count Unique Text Values with Criteria in Excel (5 Methods)****How to Count Unique Values in Multiple Columns in Excel (5 Ways)**

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!

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 VBAcode compatible with several unique values, as many as you want to be more specific.Excel VBA Code:I hope this will achieve your goal. Feel free to contact us again with any other inquiries or concerns.

Regards

Lutfor Rahman Shimanto