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