FREQUENCY(data_array, bins_array)

Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array

data_array - is an array of or reference to a set of values for which you want to count frequencies.

bins_array - is an array of or reference to intervals into which you want to group the values in the data_array

Note! The FREQUENCY function returns two or more values in an 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_arraybins_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: {3230}.

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