Author: Oscar Cronquist Article last updated on March 13, 2018

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.



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
(Excel 2007 Workbook *.xlsx)

Download excel 97-2003 example file
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Counts the number of cells within a range that meet the given condition

Adds all the numbers in a range of cells

ROW(reference) returns the rownumber of a reference

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