## How to use FREQUENCY function

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

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

### Category: Excel

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

Comments(161) Filed in category: Charts, Excel, Interactive

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

### Leave a Reply

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

<code>your formula</code>

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