## Count unique distinct values in a large dataset with a date criterion

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**

- Create a table
- Sort the table
- Two simple formulas
- Count unique distinct filtered values
- Apply date filters

**Create an excel table**

- Select data range
- Click "Insert" tab on the ribbon
- Click "Table" button
- Click OK.

**Sort Products**

- Click black arrow near header "Products"
- Click "Sort A to Z"

**Create a new formula in cell C2**

- Select cell C2
- Type formula in cell:
=IF(SUBTOTAL(103, B2)=0, C1, B2)
- Press Enter

**Create a new formula in cell D2**

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

- Select a cell below the table
- Type in cell:
=SUM(D2:D18)
- Press Enter

**Filter dates**

- Click black arrow on table header "Dates"
- Filter dates

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**

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter […]

Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]

### 3 Responses to “Count unique distinct values in a large dataset with a date criterion”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Very good idea, perfect.

Good day, is there a way to do this in a single cell formula?

Lionelo Espina,

I am not sure what you want to do, have you read this?

https://www.get-digital-help.com/2011/05/23/count-unique-distinct-records-between-two-dates-and-a-condition-in-excel-2007/

or do you want to count unique distinct records in a filtered table?

https://www.get-digital-help.com/2012/07/23/count-unique-distinct-values-in-a-filtered-table/