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.

**Table of Contents**hide

**Download to Practice**

**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 by design, 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 Excel Microsoft 365 to implement these examples.*

## Examples of Excel FREQUENCY Function

**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 occurred 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 occurred 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 occurred 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 used the range **C4:C14** as **value1**.

Then, divided the frequently occurred 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 occurred 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 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 ID**.

**Similar Readings**

**How to Use MODE Function in Excel (4 Examples)****Use VAR Function in Excel (4 Examples)****How to Use PROB Function in Excel (3 Examples)****Use Excel STDEV Function (3 Easy Examples)****How to Use Excel GROWTH Function (4 Easy Methods)**

**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 occurred 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 occurred 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 **reference**. Then in the second **ROW **function, I selected cell **B4** as **reference**. And added **1** with the cell reference.

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

**Read More:** **The Different Ways of Counting in Excel**

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

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