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.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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 […]
Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
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 press with left mouse […]
Count unique distinct records (rows) in a Pivot Table
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
Unique distinct records sorted based on count or frequency
Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]
Filter unique distinct records with a condition
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
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.
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