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:

Count unique cells by date in excel

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

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.

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Count unique distinct records in a date range and a numeric range in excel
  2. Count date records between two dates in a range in excel
  3. Count records between two dates in excel
  4. Count unique distinct values using date criteria in a range in excel
  5. Count unique distinct values in two columns with date criteria in excel
  6. Count unique values and unique distinct values in two ranges combined
  7. Count unique values and unique distinct values in three ranges combined in excel
  8. Count unique and unique distinct values in a multicolumn range in excel
  9. Most common value between two dates in a range in excel
  10. Count unique distinct months in excel