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, 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".