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