Question:I have a question about counting unique values from multiple ranges with
multiple conditions.
A1:A3 are filled with dates 2009,2008,2009
B1:B3 are filled with regular values that we need to decide whether they are
unique or not, let say 1,2,1
C1:C3 are filled with dates (2009,2008,2009)
D1:D3 1,2,1 (same as B1:B3)

The answer should be 1. the total number of unique values for column B and D
for the date 2009 is 1.

Answer:

unique-distinct-values-in-two-columns-with-date-criteria

Excel 2007 formula in D12:

=SUM(IF(IFERROR(MATCH(IF(YEAR(Dates_list1)=2009, List1, 0), IF(YEAR(Dates_list1)=2009, List1, ""), 0), 0)>=(ROW(List1)-MIN(ROW(List1))+1), 1, 0))+SUM(IF(ISERROR(MATCH(IF(YEAR(Dates_list2)=2009, List2, 0), IF(YEAR(Dates_list1)=2009, List1, ""), 0)>=(ROW(List2)-MIN(ROW(List2))+1)), IF(IFERROR(MATCH(IF(YEAR(Dates_list2)=2009, List2, 0), IF(YEAR(Dates_list2)=2009, List2, ""), 0), 0)>=(ROW(List2)-MIN(ROW(List2))+1), 1, 0), 0)) + CTRL + SHIFT + ENTER

Earlier excel versions, array formula in D12:

=SUM(IF(FREQUENCY(IF(YEAR(Dates_list1)=2009, MATCH(List1, List1, 0)), ROW(List1)-MIN(ROW(List1))+1)>0, 1))+SUM(IF(FREQUENCY(IF(YEAR(Dates_list2)=2009, MATCH(List2, List2, 0)), ROW(List2)-MIN(ROW(List2))+1)>0, 1))-SUM(IF(FREQUENCY(IF(YEAR(Dates_list2)=2009, IF(ISERROR(MATCH(List2, (YEAR(Dates_list1)=2009)*List1, 0)), FALSE, MATCH(List2, List1, 0))), ROW(List2)-MIN(ROW(List2))+1)>0, 1)) + CTRL + SHIFT + ENTER

Named ranges
List1 (B3:B5)
List2 (D4:D6)
Dates_list1 (A3:A5)
Dates_list2 (C4:C6)
What is named ranges?

Download  excel 2007 example file
unique-distinct-list-from-two-columns-with-date-criteria.xlsx
(Excel 2007 Workbook *.xlsx)

Download excel 97-2003 example file
unique-distinct-list-from-two-columns-with-date-criteria.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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

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

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

SUM(number1,[number2],)
Adds all the numbers in a range of cells

ROW(reference) returns the rownumber of a reference

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

This blog article is one out of thirteen articles on the same subject "count unique and unique distinct".

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Count unique distinct values using date criteria in a range in excel
  2. Count unique distinct values in three columns combined in excel
  3. Count unique distinct values in two columns in excel
  4. Count unique distinct values in a column in excel
  5. Count matching cell values in two columns in excel
  6. Extract a unique distinct list from two columns using excel 2007 array formula
  7. Comparing two columns and sum unique values using array formula in excel
  8. Extract a unique distinct list from three columns in excel
  9. Count unique distinct months in excel
  10. Count unique distinct records in a date range and a numeric range in excel