davidlim asks :

i have ~100K rows, and Excel is literally stalled when running the formula.

for the time being, i'm using a Pivottable and using a COUNTA function to count unique distinct value. Not automated but it's near-instantaneous to get the number 

have tried lazyvba's code. works fine, but it is not efficient (crawling for list more than >100K rows).

my pivottable is simple: dates and products. no other columns, formulas, etc.

Answer:

This blog post demonstrates how to count unique distinct filtered values in a large data set using excel 2007 table and simple formulas. Read this post if you have a smaller data set: Count unique distinct values in a column in excel

Table of contents

1. Create a table
2. Sort the table
3. Two simple formulas
4. Count unique distinct filtered values
5. Apply date filters

Create an excel table

1. Select data range
2. Click "Insert" tab on the ribbon
3. Click "Table" button
4. Click OK.

Example,

Sort Products

1. Click black arrow near header "Products"
2. Click "Sort A to Z"

Create a new formula in cell C2

1. Select cell C2
2. Type formula in cell:
   =IF(SUBTOTAL(103, B2)=0, C1, B2)
3. Press Enter

Create a new formula in cell D2

1. Select cell D2
2. Type formula in cell:
   =(C2<>C1)*1
3. Press Enter

Count values in column D

1.  Select a cell below the table
2. Type in cell:
   =SUM(D2:D18)
3. Press Enter

Filter dates

1. Click black arrow on table header "Dates"
2. Filter dates

Final notes

The formulas used in this example are simple and fast. This technique should work on a really large data set.

Download excel 2007 file *.xlsx

davidlim.xlsx

Related posts:

Excel udf: Count unique distinct values in a large dataset

Excel udf: Remove duplicates from a large dataset

Count unique distinct values using date criteria in a range in excel

Count unique distinct values in two columns with date criteria in excel

Filter unique distinct and duplicate values from a large data set in excel 2007