Return the count of each value occurs in range.
The Excel FREQUENCY function returns the total number of occurrences of a value in a given bin of data set. For example, if A1:A300 contains a list of numbers ranging between 1-100 and user wants to find the occurrence within 11 to 20 bins.
data_array – Range of data to get the frequency from.
bins_array – A series of Interval (bins) that divide the entire range of value.
Count of values per bin
- The FREQUENCY function is an Array function that returns multiple results falling between each bin at once.
- The Excel COUNTIF or COUNTIFS function is alternative to the FREQUENCY function.
- The function ignores empty cells and text in data_array.
- Note: The function returns the top of each bin; like a 20 bin is for holding the count of how many values fall between 11 and 20.
- Caution: Make sure the bin values are in ascending order.
- Cannot change part of the Array formula i.e., one cell of a range. You have to update from the first cell and then Ctrl+Shift+Enter.
- Select all Excel cell in advance to return value.
In the case of a duplicate bin, the function returns zero for the next interval. This is an important aspect which is used for creating a robust formula.
The FREQUENCY function only returns results vertically i.e., all counts in one column. Use the TRANSPOSE function to get results horizontally.