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:

Excel 2007, array formula in cell D4

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, 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 values in a column in excel
• Count unique distinct values in two columns in excel
• Count unique distinct values in three columns combined in excel
• Count unique and unique distinct values in a multicolumn range in excel
  
• Count unique values and unique distinct values in two ranges combined
  
• Count unique values and unique distinct values in three ranges combined in excel
• How to count unique combined column values
• How to count unique distinct records in a date range
  
• Count unique distinct records in a date range and a numeric range in excel
  
• Count unique distinct values in two columns with date criteria in excel
  
• Count unique distinct months in excel
• Count duplicate distinct values in a column in excel
• Most common value between two dates in excel