In Excel sheets, we store various types of data to get the insight of data. Sometimes to analyze any data you may need to calculate the frequency of a data value within a range. The Excel **FREQUENCY **function helps to figure out how many times a data value has occurred amongst a given set of values. It provides a vertical array of numbers that correspond to each value’s frequency within a range.

In this article, I will show you various examples of using the Excel **FREQUENCY **function.

**Basics of FREQUENCY Function: Summary & Syntax**

__Summary__

The Excel **FREQUENCY **function returns how often numeric values occurred within the ranges you specify in a bin table of a set of data or dataset. It will calculate and return a frequency distribution. This function returns the distribution as a vertical array of numbers that represent a count per bin. Remember one thing, the **FREQUENCY** function always returns an array with one more item than bins in the bins_array. This is designed to catch any values greater than the largest value in the bins_array.

__Syntax__

**FREQUENCY (data_array, bins_array)**

__Arguments__

Arguments |
Required/Optional |
Explanation |
---|---|---|

data_array |
Required | It is an array of values for which you want to get frequencies. |

bins_array |
Required | It is an array of intervals for grouping values. The interval is also known as “bins”. |

__Return Value__

The **FREQUENCY **function returns the frequency of values in a data set.

__Version__

*The FREQUENCY function is available for Excel 2003 and later.*

*I’m using Microsoft Excel 365 to implement these examples.*

## Excel FREQUENCY Function: 6 Examples

**1. Using The FREQUENCY Function to Get Often Occurred Values**

You can use the **FREQUENCY** function to get often occurred values by using the **FREQUENCY** function.

Here, I want to get the often-occurred values from the *Salary *column

⏩ In cell **F4**, type the following formula to get frequently occurring values.

`=FREQUENCY(C4:C14,D4:D10)`

Here, in the **FREQUENCY **function, I selected the range **C4:C14 **as **data_array** and also selected the range **D4:D10** as **bins_array**.

Now, press **ENTER**, and the **FREQUENCY **function will return the frequently occurring values from the selected range.

**2. Using The FREQUENCY Function to Figure Out Frequency Percentages **

To figure out frequency percentages you can use the **FREQUENCY** function and **the COUNT function**. To do that first find out the frequent values then use the command from ribbon to convert the values in percentage.

Here, I want to get the percentage of often occurring values from the *Salary* column.

⏩ In cell **F4, **type the following formula to get frequently occurring values.

`=FREQUENCY(C4:C14,D4:D10)/COUNT(C4:C14)`

Here, in the **FREQUENCY **function, I selected the range **C4:C14 **as **data_array** and also selected the range **D4:D10** as **bins_array**.

In the **COUNT **function the range **C4:C14** is **value1**.

Then, divide the frequently occurring values by the count of the selected range.

Now, press **ENTER**, and the **FREQUENCY **function will return the result.

To convert the result into a percentage,

First, select the cell range** (G4:G11)**

Open the **Home** tab >> from **Number **group >> select **%**.

Hence, the result will be converted into percentages.

**3. Using Excel FREQUENCY & SUM Function with Operator**

To get the sum of any frequently occurring values you can use the **FREQUENCY **function along with** the SUM function**.

Here, I want to sum up the *Student ID* who failed in particular subjects.

⏩ In cell **E4, **type the following formula.

`=SUM(--(FREQUENCY(B4:B14, B4:B14) >0))`

Here, in the **SUM **function, I used **FREQUENCY(B4:B14, B4:B14) >0) **as **number1**.

In the **FREQUENCY **function, I selected the range **B4:B14, B4:B14 **as **data_array**, and also selected the range **B4:B14, B4:B14** as **bins_array**. Then, used **>0** to get the values occurring frequently.

Now, the **SUM **function will sum up the frequently occurring students’ IDs.

*Here, I used (–) to get the result in binary form.*

Now, press **ENTER**, and the **FREQUENCY **and the **SUM **function will return the sum of frequently occurred* Student IDs*.

**4. Using Excel TRANSPOSE & FREQUENCY Function to Get Horizontal Array of Results**

The **FREQUENCY** function always returns a vertical array of results but if you want you can get a horizontal array of results by using **the TRANSPOSE function** along with the **FREQUENCY **function.

Here, I want to get the often occurring values from the *Salary* column horizontally.

⏩ In cell **F4, **type the following formula to get frequently occurring values.

`TRANSPOSE(FREQUENCY(C4:C14,D4:D10))`

Here, in the **TRANSPOSE **function, I used **FREQUENCY(C4:C14,D4:D10) **as an array.

In the **FREQUENCY **function, I selected the range **C4:C14 **as **data_array**, and also selected the range **D4:D10** as **bins_array**.

Now, the **FREQUENCY **function will return the results vertically whereas the **TRANSPOSE **function will transpose it horizontally.

Now, press **ENTER**, and you will get the results in a horizontal array.

**5. Using Excel FREQUENCY Function to Count Unique Values in A Range with Criteria**

You also can get the count of unique values in a range while applying criteria by using the **FREQUENCY** function along with the **SUM** function, **IF function**, **MATCH function**, and **the ROW function**.

To perform this example, I’m going to use the dataset given below.

⏩ In cell **F4**, type the following formula to get the count of frequently occurring unique values based on criteria.

`=SUM(--(FREQUENCY(IF(C4:C14=F4,MATCH(B4:B14,B4:B14,0)),ROW(B4:B14)-ROW(B4)+1)>0))`

Here, in the **SUM **function, I used **FREQUENCY(IF(C4:C14=F4,MATCH(B4:B14,B4:B14,0)),ROW(B4:B14)-ROW(B4)+1)>0 **as **number1**.

In the **FREQUENCY **function, I used **IF(C4:C14=F4,MATCH(B4:B14,B4:B14,0))** as **data_array** , also used **ROW(B4:B14)-ROW(B4)+1) **as **bins_array**.Then used **greater than >0**.

Now, in the **IF **function, I used **C4:C14=F4** as **logical_test**, then used **MATCH(B4:B14,B4:B14,0)** as **value_if_true**.

After that, in the **MATCH **function, I selected the range **B4:B14** as **lookup_value**, then selected the range **B4:B14** as **lookup_array** and provided **0 **as **match_type.**

I used multiple **ROW **functions. In the first **ROW **function, I selected the range **B4:B14** as a reference. Then, in the second **ROW **function, I selected cell **B4** as a reference. And added **1** with the cell reference.

Press **ENTER**, and you will get the count of frequently occurring unique values in a range while applying criteria.

**6. Find Frequent Values for Time**

If you want, you can use the **FREQUENCY** function to get often occurred times by using the **FREQUENCY** function. By using this function, it will be easier to track employees’ entry time or customers’ visiting hours in a particular shop, etc.

Here, I want to get the often occurred time from the *Entry Time* column using the dataset given below.

⏩ In cell **F4**, type the following formula to get frequently occurring values.

`=FREQUENCY(D4:D14,E4:E8)`

Here, in the **FREQUENCY **function, I selected the range **D4:D14 **as **data_array**, and also selected the range **E4:E8** as **bins_array**.

Now, press **ENTER**, and the **FREQUENCY **function will return the frequently occurring times from the selected range.

## Things to Remember

🔺 The **FREQUENCY **function will show the **#NAME **error if you misspell the function name.

🔺 The **FREQUENCY **function will show the **#SPILL** error if one or more cells in the spill range are not completely blank.

*♦ In case you are using an Excel version that is 2003 or later remember that you will need to press CTRL + SHIFT + ENTER except in Microsoft 365.*

**Practice Section**

I’ve provided a practice sheet in the workbook to practice these explained examples.

**Download to Practice**

## Conclusion

In this article, I have shown 6 examples of the Excel **FREQUENCY **function. I also tried to cover the when and why the **FREQUENCY** function may show errors frequently. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

**<< Go Back to Excel Functions | Learn Excel**