Count unique distinct numbers across multiple sheets (3D range) in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Count values > Count unique distinct numbers across multiple sheets (3D range) in excel

← Previous post -

Filed in Count values, Excel on Mar.07, 2010. Email This article to a Friend

Count unique distinct numbers in a 3D range

Array formula in E8:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0)) + CTRL + SHIFT + ENTER

Count unique numbers in a 3D range

Array formula in E10:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)=1)) + CTRL + SHIFT + ENTER

Count duplicate numbers in a 3D range

Array formula in E12:

=SUM(--(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)>1)) + CTRL + SHIFT + ENTER

Download excel tutorial file

Count unique and duplicate numerical data entries from multiple sheets.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUM(number1,[number2],)
Adds all the numbers in a range of cells

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

Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel

Count unique and duplicate numbers from a closed workbook in excel (formula)

Count unique distinct text values in a range in excel

Count unique distinct values using date criteria in a range in excel

Extract unique distinct numbers from closed workbook in excel (formula)