## Count unique distinct records with a date and column criteria

re: Count records between two dates and a criterion

based on the example, i was looking for 1 date and 1 criterion. i slightly modify the formula to

=SUMPRODUCT(--($B$1:$B$9=$E$2), --($A$1:$A$9=$E$3)) + ENTER

[assuming E2 = 9-2-2010]

the result would be 1 (one 'JA' found on 9-2-2010 date)

but this is summation of records found on 1 date with 1 criterion. It will not work if there is *multiple* 'JA' criterion exist on the same date because SUMPRODUCT summed up the records found.

I'm curious to know...

1) What if I want to know the UNIQUE DISTINCT records found on 1 date with 1 criterion?

2) Working on >100k rows of data, this formula literally slows down Excel (heavy calculation and recalculations). Is there an alternative to speed it up? UDF? array formula?

thanks!

### Answer:

Array formula in cell D3:

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

### How the array formula in cell D3 works

**Step 1 - Count records**

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

COUNTIFS($B$6:$B$11, $B$6:$B$11, $C$6:$C$11, $C$6:$C$11, $D$6:$D$11, $D$6:$D$11)

becomes

COUNTIFS({"JA";"SH";"JA"; "JA";"JA";"SH"}, {"JA";"SH";"JA"; "JA";"JA";"SH"}, {40422;40423; 40423;40423;40423;40426}, {40422;40423; 40423;40423;40423;40426}, {"North";"South";"North"; "West";"West";"South"}, {"North";"South";"North"; "West";"West";"South"})

and returns array {1;1;1;2;2;1}

**Step 2 - Filter records using name and date criteria**

The IF function returns one value (argument2) if TRUE and another (argument3) if FALSE.

IF(($B$6:$B$11=B3)*($C$6:$C$11=C3), (1/COUNTIFS($B$6:$B$11, $B$6:$B$11, $C$6:$C$11, $C$6:$C$11, $D$6:$D$11, $D$6:$D$11)), 0)

becomes

IF(($B$6:$B$11=B3)*($C$6:$C$11=C3), (1/{1;1;1;2;2;1}), 0)

becomes

IF(({"JA";"SH";"JA";"JA";"JA";"SH"}="JA")*({40422;40423;40423;40423;40423;40426}=40423), (1/{1;1;1;2;2;1}), 0)

becomes

IF(({0;0;1;1;1;0}, (1/{1;1;1;2;2;1}), 0)

becomes

IF(({0;0;1;1;1;0}, {1;1;1;0,5;0,5;1}, 0)

and returns {0;0;1;0,5;0,5;0}

**Step 3 - Sum values**

The SUM function adds numbers an return the total.

=SUM(IF(($B$6:$B$11=B3)*($C$6:$C$11=C3), (1/COUNTIFS($B$6:$B$11, $B$6:$B$11, $C$6:$C$11, $C$6:$C$11, $D$6:$D$11, $D$6:$D$11)), 0))

becomes

=SUM({0;0;1;0,5;0,5;0}) and returns 2 in cell D3.

### Count values category

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 […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

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 press with left mouse […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]

The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]

### Unique distinct records category

Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to count unique […]

This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]

The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]

In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 5 Responses to “Count unique distinct records with a date and column criteria”

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

thanks oscar,

have tried it, it works.

however, 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 :)

nonetheless, thanks for the solution above!

davidlim,

thanks!

The vba code provided here:

https://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html

seems to count unique values in a pivot table.

hi oscar,

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.

any other suggestions?

davidlim,

Do you want to count unique distinct products between two dates?

davidlim,

read this post: Count unique distinct values in a large dataset with a date criterion