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

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

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}