Author: Oscar Cronquist Article last updated on April 14, 2018

The FREQUENCY function calculates how often values occur within a range of values and returns a vertical array of numbers.

It returns an array that is one more item larger than the bins_array.

Excel Function Syntax

FREQUENCY(data_array, bins_array)

Arguments

data_array An array or cell range for which you want to determine frequencies.
bins_array The intervals which you want to group the values in.

Note! The FREQUENCY function returns two or more values in a vertical array and you enter it as an array formula. Blanks and text strings are ignored.

Example 1

frequency function2

Array formula in cell range D3:D6:

=FREQUENCY(B3:B10,C3:C5)

Explaining the calculations

FREQUENCY(data_array, bins_array)

FREQUENCY(B3:B10,C3:C5)

becomes

FREQUENCY({1; 2; 2; 5; 4; 7; 7; 10},{2; 5; 10})

and returns a vertical array of numbers: {3; 2; 3; 0}.

There are 3 values that are smaller or equal to the first value (2) in the bins_array: 1, 2, 2.

There are 2 values that are larger than 2 and smaller or equal to the second value (5) in the bins_array: 4, 5

There are 3 values that are larger than 5 and smaller or equal to the third value (10) in the bins_array: 7, 7, 10

There are 0 (zero) values that are larger than 10 in the bins_array.

Remember! The FREQUENCY function returns an array of numbers and it has one more element than the bins_array. In this example the bins_array has 3 values, FREQUENCY function returns 4 values.

Example 2

frequency function1

Array formula in cell range F3:F6:

=FREQUENCY(IF(B3:B10="A",C3:C10,""),E3:E5)

Explaining the calculations

Step 1 - Filter values

IF(B3:B10="A",C3:C10,"")

becomes

=IF({"A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"}="A",{1; 2; 2; 5; 4; 7; 7; 10},"")

and returns

{1; ""; 2; ""; 4; ""; 7; ""}

Step 2 - Frequency

FREQUENCY(IF(B3:B10="A",C3:C10,""),E3:E5)

becomes

FREQUENCY({1; ""; 2; ""; 4; ""; 7; ""},E3:E5)

becomes

FREQUENCY({1; ""; 2; ""; 4; ""; 7; ""},{2; 5; 7})

and returns {2; 1; 1;0}

Download excel *.xlsx file

FREQUENCY function.xlsx

Functions in this post:

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Excel Function Reference

This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.