How to Use Excel FREQUENCY Function (6 Examples)

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

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.

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.

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.

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.

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.

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.

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