Here is a picture of a closed workbook

How to count unique distinct text values in a closed workbook

This formula works with possible blank cells in range A1:A6.

=SUM(IF(ISBLANK('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6), 0, IF(MATCH('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 'C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 0)=ROW('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)-MIN(ROW('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6))+1, 1, 0))) + CTRL + SHIFT + ENTER

'C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6 is the full path to the closed workbook. Make sure you edit this path.

How to count duplicate text values in a closed workbook

This formula works also with possible blank cells in range A1:A6.

=SUM(IF(ISBLANK('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6), 0, IF(MATCH('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 'C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6, 0)=ROW('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6)-MIN(ROW('C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6))+1, 0, 1))) + CTRL + SHIFT + ENTER

'C:\temp\[closed workbook.xls]Sheet1'!$A$1:$A$6 is the full path to the closed workbook. Make sure you edit this path.

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value