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.

Overview of Excel FREQUENCY Function

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


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)

Using The FREQUENCY Function to Get Often Occurred 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 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)

Using The Excel FREQUENCY Function to Figure Out Frequency Percentages 

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

Using The Excel FREQUENCY Function to Figure Out Frequency Percentages 

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

Using Excel FREQUENCY & SUM Function with Operator

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

In the FREQUENCY function, I selected the rangeB4: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


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

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

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.

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

⏩ 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))

Using Excel FREQUENCY Function to Count Unique Values in A Range with 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 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.

Using Excel FREQUENCE Function to Find Frequent Values for Time

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

Using Excel FREQUENCE Function to Find Frequent Values for Time


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.

Practice Excel FREQUENCY Function


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.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo