How to count unique distinct records in a date range

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > How to count unique distinct records in a date range

← Previous post -

Filed in Excel on Mar.06, 2009. Email This article to a Friend

Question: How do I count unique distinct records between two dates?

Answer:

The dates don´t have to be sorted.

Excel 2003, array formula in cell D4:

=SUM(IF((MATCH(INDIRECT("B"&MATCH(D1, A3:A37)&":B"&MATCH(D2, A3:A37)), INDIRECT("B"&MATCH(D1, A3:A37)&":B"&MATCH(D2, A3:A37)), 0))>=(ROW(INDIRECT("B"&MATCH(D1, A3:A37)&":B"&MATCH(D2, A3:A37)))-(MIN(ROW(INDIRECT("B"&MATCH(D1, A3:A37)&":B"&MATCH(D2, A3:A37))))-1))=TRUE, 1, 0)) + CTRL + SHIFT + ENTER

Excel 2007, array formula in cell D4

=SUMPRODUCT(IF((Date_rng<=F3)*(Date_rng>=F2), 1/COUNTIFS(Date_rng, "<="&F3, Date_rng, ">="&F2, Text_rng, Text_rng), 0)) + CTRL + SHIFT + ENTER

Yes, you must press CTRL + SHIFT + ENTER after copying the above formula into formula bar.

You can easily add more columns to the array formula, after criteria in COUNTIFS function. The array formula will then return unique distinct records (rows) between selected dates.

Explaining excel 2007 array formula

=SUMPRODUCT(IF((Date_rng<=F3)*(Date_rng>=F2), 1/COUNTIFS(Date_rng, "<="&F3, Date_rng, ">="&F2, Text_rng, Text_rng), 0))

Step 1 - Count the number of records specified by a given set of criteria

=SUMPRODUCT(IF((Date_rng<=F3)*(Date_rng>=F2), 1/COUNTIFS(Date_rng, "<="&F3, Date_rng, ">="&F2, Text_rng, Text_rng), 0))

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...) counts the number of cells specified by a given set of conditions or criteria
1/COUNTIFS(Date_rng, "<="&F3, Date_rng, ">="&F2, Text_rng, Text_rng)

becomes

1/COUNTIFS(D{39448; 39449; 39450; 39451; 39452; 39453; 39454; 39455; 39456; 39457; 39458; 39459; 39460; 39461; 39462; 39463; 39464; 39465; 39466; 39467; 39468; 39469; 39470; 39471; 39472; 39473; 39474; 39475; 39476; 39477; 39478; 39479; 39480; 39481; 39482}, "<="&39468,{39448; 39449; 39450; 39451; 39452; 39453; 39454; 39455; 39456; 39457; 39458; 39459; 39460; 39461; 39462; 39463; 39464; 39465; 39466; 39467; 39468; 39469; 39470; 39471; 39472; 39473; 39474; 39475; 39476; 39477; 39478; 39479; 39480; 39481; 39482}, ">="&39464, {"II"; "CC"; "EE"; "JJ"; "HH"; "BB"; "GG"; "FF"; "HH"; "EE"; "GG"; "DD"; "CC"; "EE"; "HH"; "HH"; "CC"; "GG"; "GG"; "II"; "CC"; "FF"; "DD"; "AA"; "JJ"; "FF"; "AA"; "FF"; "CC"; "HH"; "DD"; "DD"; "FF"; "FF"; "FF"}, {"II"; "CC"; "EE"; "JJ"; "HH"; "BB"; "GG"; "FF"; "HH"; "EE"; "GG"; "DD"; "CC"; "EE"; "HH"; "HH"; "CC"; "GG"; "GG"; "II"; "CC"; "FF"; "DD"; "AA"; "JJ"; "FF"; "AA"; "FF"; "CC"; "HH"; "DD"; "DD"; "FF"; "FF"; "FF"})

becomes

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

becomes

{1; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; 0,5; 0,5; 1; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!}

Step 2 - Filter values where corresponding dates are a match

=SUMPRODUCT(IF((Date_rng<=F3)*(Date_rng>=F2), 1/COUNTIFS(Date_rng, "<="&F3, Date_rng, ">="&F2, Text_rng, Text_rng), 0))

IF(logical_test;[value_if:true];[value_if_false]) checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

IF((Date_rng<=F3)*(Date_rng>=F2), 1/COUNTIFS(Date_rng, "<="&F3, Date_rng, ">="&F2, Text_rng, Text_rng), 0)

becomes

IF((Date_rng<=F3)*(Date_rng>=F2), {1; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; 0,5; 0,5; 1; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!}, 0)

becomes

IF(({39448; 39449; 39450; 39451; 39452; 39453; 39454; 39455; 39456; 39457; 39458; 39459; 39460; 39461; 39462; 39463; 39464; 39465; 39466; 39467; 39468; 39469; 39470; 39471; 39472; 39473; 39474; 39475; 39476; 39477; 39478; 39479; 39480; 39481; 39482}<=39468)*({39448; 39449; 39450; 39451; 39452; 39453; 39454; 39455; 39456; 39457; 39458; 39459; 39460; 39461; 39462; 39463; 39464; 39465; 39466; 39467; 39468; 39469; 39470; 39471; 39472; 39473; 39474; 39475; 39476; 39477; 39478; 39479; 39480; 39481; 39482}>=39464), {1; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; 0,5; 0,5; 1; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 0,5; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!}, 0)

returns this array

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0,5;0,5;0,5;1;0,5;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

Step 3 - Sum values

=SUMPRODUCT(IF((Date_rng<=F3)*(Date_rng>=F2), 1/COUNTIFS(Date_rng, "<="&F3, Date_rng, ">="&F2, Text_rng, Text_rng), 0))

becomes

=SUMPRODUCT({0; 0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0,5; 0,5;0,5;1;0,5;0;0;0;0;0; 0;0;0;0;0;0;0;0;0})) and returns 3.

Download excel sample file for this tutorial.

count-unique-records-between-a-date-range.xls
(Excel 97-2003 Workbook *.xls)

count-unique-distinct-records-between-dates.xlsx
(Excel 2007 Workbook *.xls)

Functions in this tutorial:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
returns the row number of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

INDIRECT(ref_text;[a1])
Returns the reference specified by a text string

SUM(number1,[number2],)
Adds all the numbers in a range of cells

MIN(number1;[number2];
Returns the smallest number in a set of values. Ignores logical values and text.

This blog article is one out of twelve articles on the same subject "count unique and unique distinct".

Count unique distinct records in a date range and a numeric range in excel

Count unique records between two dates in excel

Count unique distinct records with a date and column criteria in excel 2007

Count unique distinct records in excel 2007

Count unique distinct values using date criteria in a range in excel

Recommended categories

Excel

Comments (5)

5 Responses to “How to count unique distinct records in a date range”

Ian says:
September 14, 2009 at 4:55 pm
This is great!
Is there anyway to do this based on date and another criteria? Say if C had dollar values and you wanted to only find uniques that were between a certain $ value and a certain date?
Reply
admin says:
September 15, 2009 at 10:16 pm
Ian,
see this blog post: http://www.get-digital-help.com/2009/09/15/count-unique-distinct-records-in-a-date-range-and-a-numeric-range-in-excel/
Reply
Ian says:
September 17, 2009 at 3:03 pm
Thanks so much! I just found the article before checking the comments here. It will really help me out!
Reply
Count unique records between two dates in excel | Get Digital Help says:
March 16, 2011 at 11:02 am
[...] you are looking for unique distinct values, read this article:How to count unique distinct records in a date range [...]
Reply
Counting unique values in date range says:
November 30, 2012 at 11:21 pm
[...] range Hello, I am trying to count unique values in a date range. I found this great sample: How to count unique distinct records in a date range | Get Digital Help - Microsoft Excel resource However my date range comes from a pivot table grouped by 7 days. So my data is like: Date, Item [...]
Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

Subscribe
Blog updates by email:
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.
Don´t forget to add my RSS feed and subscribe to new blog articles by email.
Recent Comments
- Oscar on Populate a list box with visible unique values from an excel table (vba)
- Oscar on Sum values in a range where adjacent cell value equals a criterion in excel
- aMareis on Highlight unique distinct values in two ranges combined using conditional formatting in excel
- G. Cooley on Highlight events in a calendar
- Alaa Abdullnabi on Merge two columns with possible blank cells in excel (formula)
- Oscar on Merge two columns with possible blank cells in excel (formula)
- Oscar on Calculate your stock portfolio performance in excel
- Oscar on Formula for matching a date within a date range in excel
- Ag on Excel vba: Save invoice data
- Oscar on Create a unique distinct list of a long list without sacrificing performance using vba in excel
Recent Posts