How Do You Count Unique Cell Entries In A Column With Many Rows Without Using Table Array

In Excel 2010, how do I count all the unique data entries (Text Entries) in a column (3,000+ rows) to find out the count of unique entries without using a table array? I do not want to use the SUM(IF(FREQUENCY(IF(LEN,MATCH formula because of it being necessary to create a table array.

Answers go into Executive Dashboard Screen, so to have executives have to make manual updates would not be found as "acceptable".

The data table is constantly updated, so the solution must be dynamic. I need to further filter the data based on data contained for the previous 365 days. PivotTable does not work because filtering for dates can only be done if you enter the dates manually from the date filter screen. Likewise Filter function would require manual actions.

A =COUNTUNIQUEIFS() function would be great Microsoft!


Example Spreadsheet.jpg

In the example given, if today was 1/15/2014 the number of unique projects would be calculated as 4.

There are lots of formulas to count unique values, depending on particulars. Here's one:

=SUMPRODUCT(1/ COUNTIF(A1:A10, A1:A10))

For the count you can use a dynamic defined range.

Where is the data located?

What type of data is it? Is it text? Numeric? Could be both? Something else?

Is the data in a contiguous range (no empty cells within the data range)?