Here is a picture of the closed workbook. There can´t be any blank cells or text values in your range.

As you can see, there are five unique distinct values (1, 2, 3, 5 and 6) and one duplicate value (3).

How to count unique and duplicate numbers from a closed workbook

Formula in B1:

=SUM(IF(FREQUENCY('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6;''C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)>0;1;0)) + ENTER

Don´t forget to change path to your closed workbbook.

Formula in B2:

=SUM(IF(FREQUENCY('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6;''C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)=0;1;0)) - 1 + ENTER

Don´t forget to change path to your closed workbbook

Evaluate formula

If I try to evaluate formula I get som strange results. A lot of #ref errors but the final result is correct.

Functions in this article:

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

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.