# Count unique distinct records

#### Table of Contents

## 1. Count unique distinct records

The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count unique distinct records from this table but Excel can do that for us. A record is an entire row in the table above.

Example, Row 3 has a duplicateÂ in row 9. Row 4 has a duplicateÂ in row 6. Row 5 has a duplicateÂ in row 8. Row 7 is unique meaning there is only one instance of that record in the table. It is also possible to highlight unique distinct records using conditional formatting.

Formula in cell B12:

### 1.1 Watch a video where I explain the formula

We can verify the count above in cell B12 by extracting all unique distinct records from the above table. I am using a formula from this blog article:

Filter unique distinct row records

### 1.2 Explaining formula in cell B12

#### Step 1 - Count each record in data set

COUNTIFS(C3:C9,C3:C9,D3:D9,D3:D9,E3:E9,E3:E9) counts the number of times all criteria match on each row

Example,

The first record is Bike, 1, Color: Blue. The only rows where these criteria match is the first one and last. So the first number in the returning array is 2.

I have entered =COUNTIFS(C3:C9,C3:C9,D3:D9,D3:D9,E3:E9,E3:E9) in column E.

COUNTIFS(C3:C9,C3:C9,D3:D9,D3:D9,E3:E9,E3:E9) returnsÂ {2;2;2;2;1;2;2}

#### Step 2 - Divide 1 with array

Why divide 1 with array? If there are two instances of a value the sum returns 1 (0.5 + 0.5 = 1), three instances returns 1 (1/3 + 1/3 + 1/3 = 1) and so on. This lets you count all instances of a value as one.

1/COUNTIFS(C3:C9,C3:C9,D3:D9,D3:D9,E3:E9,E3:E9)

becomes

1/{2;2;2;2;1;2;2}

and returnsÂ {0.5;0.5;0.5;0.5;1;0.5;0.5}

#### Step 3 - Sum values in array

The SUMPRODUCT function lets you sum values, in this case, without entering it as an array formula.

SUMPRODUCT(1/COUNTIFS(C3:C9,C3:C9,D3:D9,D3:D9,E3:E9,E3:E9))

becomes

SUMPRODUCT({0.5;0.5;0.5;0.5;1;0.5;0.5})

and returns 4 in cell range B12

## 2. Count records with possible blank rows in data set

Array formula in cell A28:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

## 3. How to count blank rows/records

Formula in B28:

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

More than 1300 Excel formulas### Excel categories

### 6 Responses to “Count unique distinct records”

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

Respected Sir

i like to count p+p+p+p+p=5 . i write 'p' in five colum & total numeric 5 autometicaly in sixth colum how i do it.