Author: Oscar Cronquist Article last updated on January 11, 2018

This blog article is one out of  four articles on the same subject.

Question: How do I count unique records in a date range?

Unique records are values listed only once. In the example below only value "II" is unique. All other values have duplicates within the date range.

If you are looking for unique distinct values, read this article:How to count unique distinct records in a date range

### Formula in C44:

=SUM(IF(FREQUENCY(IF(ABS(Date_rng-(\$B\$40+\$B\$41)/2)<=(\$B\$41-\$B\$40)/2, COUNTIF(Text_rng, "<"&Text_rng), ""), IF(ABS(Date_rng-(\$B\$40+\$B\$41)/2)<=(\$B\$41-\$B\$40)/2, COUNTIF(Text_rng, "<"&Text_rng), ""))=1, 1, 0)) + CTRL + SHIFT + ENTER

### Alternative array formula in cell C44

=SUMPRODUCT(IF(IF((Date_rng<=B41)*(Date_rng>=B40), 1/COUNTIFS(Date_rng, "<="&B41, Date_rng, ">="&B40, Text_rng, Text_rng), 0)=1, 1, 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 alternative array formula

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

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

=SUMPRODUCT(IF(IF((Date_rng<=B41)*(Date_rng>=B40), 1/COUNTIFS(Date_rng, "<="&B41, Date_rng, ">="&B40, Text_rng, Text_rng), 0)=1, 1, 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, "<="&B41, Date_rng, ">="&B40, Text_rng, Text_rng)

becomes

1/COUNTIFS({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({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(IF((Date_rng<=B41)*(Date_rng>=B40), 1/COUNTIFS(Date_rng, "<="&B41, Date_rng, ">="&B40, Text_rng, Text_rng), 0)=1, 1, 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<=B41)*(Date_rng>=B40), 1/COUNTIFS(Date_rng, "<="&B41, Date_rng, ">="&B40, Text_rng, Text_rng), 0)

becomes

IF((Date_rng<=B41)*(Date_rng>=B40), {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 - Filter unique values

=SUMPRODUCT(IF(IF((Date_rng<=B41)*(Date_rng>=B40), 1/COUNTIFS(Date_rng, "<="&B41, Date_rng, ">="&B40, Text_rng, Text_rng), 0)=1, 1, 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(IF((Date_rng<=B41)*(Date_rng>=B40), 1/COUNTIFS(Date_rng, "<="&B41, Date_rng, ">="&B40, Text_rng, Text_rng), 0)=1, 1, 0)

becomes

IF({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}=1, 1, 0)

returns this array

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

Step 4 - Sum values

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

becomes

=SUMPRODUCT({0;0;0;0;0;0; 0;0;0;0;0;0;0;0;0;0; 0;0;0;1;0;0;0;0;0;0;0; 0;0;0;0;0;0;0;0})) returns 1.

Named ranges
Date_rng (A2:A169)
Text_rng (B2:B169)
What is named ranges?

count-unique-records-between-a-date-range.xls
(Excel 97-2003 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

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

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

ABS(number)
Returns the absolute value of a number, a number without its sign.

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

This blog article is one out of  four articles on the same subject.