The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of each value in a range. FREQUENCY returns multiple values and must be entered as an array formula with control-shift-enter.
Get the frequency of values in a data set
A vertical array of frequencies
=FREQUENCY (data_array, bins_array)
data_array - An array of values for which you want to get frequencies.
bins_array - An array of intervals ("bins") for grouping values.
FREQUENCY counts how often values occur in a set of data. You can use Excel's FREQUENCY function to create a frequency distribution - a summary table that shows the frequency (count) of each value in a range. It returns a vertical array of numbers that represent frequencies, and must be entered as an array formula with control + shift + enter.
For example, with data in A1:A100, and bins in B1:B5, you would select C1:C5 and enter a formula like this:
To calculate the longest winning streak, you can use an array formula based on the frequency function.
Note: FREQUENCY must be entered as an array formula using Control + Shift + Enter
How this formula works
To count consecutive monthly orders, you can use an array formula based on the FREQUENCY function, with help from COLUMN and MAX.
In the example shown, the formula in I5 is:
To count unique values in a range with a criteria, you can use an array formula based on the FREQUENCY function.
Assume you have a list of employee names together with hours worked on "Project X", and you want know how...
To count unique numeric values in a range with criteria you can use a formula based on the SUM and FREQUENCY functions, together with the IF function to apply criteria.
For example, assume you have a list of employee...
If you need to count unique text values in a range, you can use a formula that uses several functions: FREQUENCY , MATCH, ROW and SUMPRODUCT.
It's also possible to use COUNTIF, as explained below.
Assume you have a...
To count unique numeric values in a range, you can use a formula based on the FREQUENCY and SUM functions.
For example, assume you have a list of employee numbers together with hours worked on "Project X", and you want...
The Excel MODE function returns the most frequently occurring number in a numeric data set. For example, =MODE(1,2,4,4,5,5,5,6) returns 5.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.