## How to use the FREQUENCY function

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.

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.

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

**Table of contents**

## 1. FREQUENCY function example

(Array) formula in cell range D3:D6:

### 1.1 How to enter an array formula

- Select cell range D3:D6
- Type the formula.
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once. Release 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.

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

## 2. FREQUENCY function with a condition

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:

### 2.1 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 press with left mouse button on the "Evaluate Formula" button. A dialog box appears, press with left mouse button on the "Evaluate" button to move to the next step in formula calculation. Press with left mouse button on "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:

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.

## 3. FREQUENCY function and 3D ranges

This example demonstrates how to use the FREQUENCY function with 3D references. 3D ranges are cell ranges across worksheets meaning a cell reference to a specified number of worksheets in a workbook.

This lets you work with data from multiple worksheets using the FREQUENCY function, I will show you how to build such formula. Other Excel functions that can work with 3D ranges are SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA functions

Array formula in cell D3:

=FREQUENCY('3D range:3D range1'!B3:B10, B3:B10)

### 3.1 How to enter a formula containing 3D ranges

The formula contains a 3-D reference to a range of worksheet names, make sure you rearrange the worksheets so only worksheets you want to include are being used.

- Select cell range D3:D10.
- Press with left mouse button on in the formula bar, the prompt appears.
- Type =FREQUENCY(
- Press and hold SHIFT key.
- Select the last worksheet you want to include in the FREQUENCY function.
- Release SHIFT key.
- Select cell range B3:B10 with the mouse.
- Type , (comma)
- Select cell range B3:B10 with the mouse.
- Now enter the formula as an array formula (Excel 365 users can skip this step and simply press Enter).
- Here are the steps sto enter an array formula.
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.

The formula has now a leading and trailing curly bracket, like this: {=array_formula]

Don't enter these characters yourself, they appear automatically. The image above does not show these characters because I use Excel 365 which uses dynamic array formulas that automatically spill values to cells below.

### 3.2 How to rearrange worksheets

The image above shows three worksheet names, "3D range", "Sheet7", and "3D range1". We want to move worksheet "Sheet7" so it is not included in the FREQUENCY function.

- Press and hold with mouse on worksheet name "Sheet7".
- Drag with mouse to move the worksheet tab to a new location.
- Release the mouse button to release the worksheet tab.

The image above shows "Sheet7" in a new location, you can now create a 3D reference to worksheets "3D range" and "3D range1".

## 4. Excel file

### 'FREQUENCY' function examples

The following 19 articles have formulas containing the FREQUENCY function.

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Question: I have a question that I can’t seem to find an answer to: I want to make a full […]

The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]

The image above demonstrates three different formulas in column E that counts unique numbers unique distinct numbers duplicate numbers from […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

### Functions in 'Statistical' category

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 to Quang Nguyen

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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