## Count unique distinct numbers across multiple sheets

The image above demonstrates three different formulas in column E that counts

- unique numbers
- unique distinct numbers
- duplicate numbers

from multiple sheets in the same workbook.

### Count unique distinct numbers

The following formula counts unique distinct numbers in multiple sheets (3D range):

Formula in E8:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell E8

#### Step 1 - Count numbers

The FREQUENCY function calculates the number of times a number exists in a cell range, it also has the ability to count numbers across multiple worksheets.

The FREQUENCY function returns the count for the corresponding number only once. Example, 3 exists twice in column B above so the function returns 2 on the same row, however, the next time 3 appears in the list the function returns 0 (zero), see row 8. We can use that to count numbers.

FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)

returns

{1; 9; 7; 0; 0; 0; 0; 0; 0; 0; 8; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0}.

#### Step 2 - Check if number in array is not equal to 0 (zero)

The less and greater than sign together means not equal to.

FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0

becomes

{1; 9; 7; 0; 0; 0; 0; 0; 0; 0; 8; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0}<>0

and returns

{TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}.

#### Step 3 - Convert boolean values to numerical equivalents

The SUMPRODUCT function can't sum boolean values so we need to convert TRUE to 1 and FALSE to 0 (zero). There are a few ways to convert them, you can add a zero or multiply with 1 or in this example use double negatives.

--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0)

becomes

--({TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns

{1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0}.

#### Step 4 - Add numbers in array

SUMPRODUCT(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0))

becomes

SUMPRODUCT({1; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0})

and returns 6.

### Count unique numbers

This formula counts unique numbers in multiple sheets (3D range)

Formula in E10:

### Count duplicate numbers

Formula in E12:

### Get excel tutorial file

Count unique and duplicate numerical data entries from multiple sheets.xls

(Excel 97-2003 Workbook *.xls)

### Count values category

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

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]

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

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

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

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]

The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### Leave a Reply

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