Author: Oscar Cronquist Article last updated on October 07, 2017

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?


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:

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.

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