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.