Count unique records between two dates in excel

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 > Count values > Count unique records between two dates in excel

← Previous post -

Filed in Count values, Dates, Excel, Unique distinct values, Unique values on Sep.13, 2009. Email This article to a Friend

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

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

Answer:

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, {"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?

Download excel sample file for this tutorial

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.

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 records in excel 2007

Count date records between two dates in a range in excel

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