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 formulasExcel 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.
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.