## How to use the FREQUENCY function

*Article 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:**

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

**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

### 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

### Articles with the 'FREQUENCY' Function

The following 9 articles have formulas that contain the FREQUENCY function.

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]### Functions in 'Statistical'

The FREQUENCY function function is one of many functions in the 'Statistical' category.

### 3 Responses to “How to use the FREQUENCY function”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar,

Often I see spreadsheets that have used the Histogram feature (in the Data Analysis Tools) to create a frequency distribution. Although this works, it has one major drawback: it is static. If the data changes, then the Histogram count does not update so it may be wrong.

Using the FREQUENCY function, as you demonstrate in this article, is a much better solution.

Cheers,

Bob.

[…] Frequency function […]

I tried frequency function many time but it does not work as shown in Example 2.

- First, I type the formula: =Frequency(B3:B10,C:C5)

- Then I press ctrl+shift+enter

- The result is only one value of 3 instead of an array as it is supposed to be

Please tell me why? Thank you