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.

Related posts:

1. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
2. Count unique and duplicate numbers from a closed workbook in excel (formula)
3. Extract unique distinct numbers from closed workbook in excel (formula)
4. Count unique distinct text values in a range in excel
5. Count unique distinct values using date criteria in a range in excel
6. Count unique distinct records in a date range and a numeric range in excel
7. Count unique distinct values within same week, month or year in excel
8. How to count unique distinct occurrences for each date in excel
9. Count unique records by date in excel
10. Count unique and duplicates text values in a closed workbook in excel (formula)