This is a question from How to count unique distinct records in a date range

Question: Is there anyway to do this based on date and another criteria? Say if C had dollar values and you wanted to only find uniques that were between a certain $ value and a certain date?

Answer:

Count unique distinct records in a date range and a numeric range

Array formula in B35:

=SUM(IF(FREQUENCY(MATCH(IF((Date_rng<=Date_to)*(Date_rng>=Date_from)*(Amount_rng<=Amount_to)*(Amount_rng>=Amount_from), Text_rng, ""), IF((Date_rng<=Date_to)*(Date_rng>=Date_from)*(Amount_rng<=Amount_to)*(Amount_rng>=Amount_from), Text_rng, ""), 0), MATCH(IF((Date_rng<=Date_to)*(Date_rng>=Date_from)*(Amount_rng<=Amount_to)*(Amount_rng>=Amount_from), Text_rng, ""), IF((Date_rng<=Date_to)*(Date_rng>=Date_from)*(Amount_rng<=Amount_to)*(Amount_rng>=Amount_from), Text_rng, ""), 0))>0, 1, 0))-IF(ROWS(Date_rng)=SUM(IF((Date_rng<=Date_to)*(Date_rng>=Date_from)*(Amount_rng<=Amount_to)*(Amount_rng>=Amount_from), 1, 0)), 0, 1) + CTRL + SHIFT + ENTER

Named ranges
Date_rng (A2:A25)
Text_rng (B2:B25)
Amount_rng (C2:C25)
Date_from (B29)
Date_to (B30)
Amount_from (B32)
Amount_to (B33)
What is named ranges?

How to implement excel array formula to your excel sheet
Change named ranges.

Download excel sample file for this tutorial.
Count unique distinct records in a date range and a numeric 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

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

ROWS(array) returns the number of rows in a reference or an array

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 records by date in excel
  2. Count date records between two dates in a range in excel
  3. Count unique and unique distinct values in a multicolumn range in excel
  4. Count unique distinct values in two columns with date criteria in excel
  5. Count unique distinct values using date criteria in a range in excel
  6. Count unique distinct months in excel
  7. Count unique values and unique distinct values in two ranges combined
  8. Count unique distinct values in three columns combined in excel
  9. Count unique distinct values in two columns in excel
  10. Count unique values and unique distinct values in three ranges combined in excel