Author: Oscar Cronquist Article last updated on March 10, 2020

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.

The image above demonstrates the FREQUENCY function cell range F3:F6.

=FREQUENCY(B3:B10, D3:D5)

This formula must be entered as an array formula, however, Excel 365 subscribers may and should enter it as a regular formula. The formula will automatically extend or "spill" values to cells below as far as needed provided the cells are empty.

Microsoft calls these formulas "dynamic arrays" and Excel returns #SPILL! error if cells below are not empty.

Follow these steps to create an array formula if you own an earlier Excel version. I am using the example above, see image above.

Select cell range F3:F6, type the formula. Press and hold CTRL + SHIFT keys simultaneously. Press Enter once. Releasee all keyboard keys.

The formula will now begin and end with a curly bracket, like this: {=array_formula}

They appear automatically, do not enter these characters yourself.

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.

Comment

The FREQUENCY function returns two or more values in a vertical array, blank cells and text strings are ignored. This means that you can only use the function with numerical values.

Example 1

frequency function2

(Array) formula in cell range D3:D6:

=FREQUENCY(B3:B10, C3:C5)

Explaining formula

The first argument is data_array and the second one is bins_array: 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.

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 function with a condition

frequency function1

The image above shows the FREQUENCY function entered in F3:F6 using an IF function to filter values based on a condition.

Array formula in cell range F3:F6:

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

Explaining the calculation

I recommend using the "Evaluate Formula" feature to learn more about formulas. This tool allows you to examine formula calculations in detail. It also lets you troubleshoot formulas, what is making my formula return an error?

Go to tab "Formulas" on the ribbon and click the "Evaluate Formula" button. A dialog box appears, click the "Evaluate" button to move to the next step in formula calculation. Click "Close" button to dismiss the dialog box.

Step 1 - Filter values

The IF function lets you return a value if a logical expression returns TRUE and another value if FALSE, the logical expression usually returns a boolean value but their equivalents work just fine. FALSE -> 0 (zero), TRUE -> any number.

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

We want to compare the values in cell range B3:B10 with "A" and if they match then return the corresponding value on the same row in cell range C3:C10. IF no match return a blank "".

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},"")

becomes

IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 2; 2; 5; 4; 7; 7; 10},"")

and returns this array: {1; ""; 2; ""; 4; ""; 7; ""}.

Step 2 - Frequency

The FREQUENCY function uses the array from the IF function in the first argument, blank values "" are ignored.

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} in cell range F3:F6.

The image above shows what the FREQUENCY function returns if you use the same numbers in the data_array and the bins_array.

Formula in cell range D3:D11:

=FREQUENCY(B3:B10, B3:B10)

The FREQUENCY function counts how many values there are for each number for the first instance of a particular number. For example, value 10 exist only once and the function returns 1.

Value 7 exists twice in the list and the corresponding value in the array returns 2, however, only the first instance returns the count. The remaining values for that particular value returns 0 (zero).

This can be used to identify unique distinct numbers in a list among many other things. Check out the FREQUENCY function category to find formulas that contains the FREQUENCY function.

Download Excel file


FREQUENCY-function.xlsx

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.