Question: How do i count unique distinct records (B1:B35) in a date range (A1:A35)?

unqiue-records-in-a-date-range

Answer: Here is the formula in D4. See picture below.

=SUM(IF((MATCH(INDIRECT("B"&MATCH(D1,A1:A35)&":B"&MATCH(D2,A1:A35)),INDIRECT("B"&MATCH(D1,A1:A35)&":B"&MATCH(D2,A1:A35)),0))>=(ROW(INDIRECT("B"&MATCH(D1,A1:A35)&":B"&MATCH(D2,A1:A35)))-(MIN(ROW(INDIRECT("B"&MATCH(D1,A1:A35)&":B"&MATCH(D2,A1:A35))))-1))=TRUE,1,0))

unqiue-records-in-a-date-range-2


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

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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Count unique distinct records in a date range and a numeric range in excel
  2. Count unique records by date in excel
  3. Count date records between two dates in a range in excel
  4. Count unique distinct months in excel
  5. Count unique distinct values using date criteria in a range in excel