Question: How do I count unique values (not date values) in a range where every second column contains dates? I want to count unique values where the year is 2009? See picture below.

Count unique distinct values using date criteria in a range


Count unique values

Unique values are values occuring only once in a range.

Array formula in D20:

=SUM(--(FREQUENCY((YEAR(IF(MOD(COLUMN(B3:F12), 2)=0, B3:F12))=C15)*(COUNTIF(C3:G12, "<"&C3:G12)+1), (COUNTIF(C3:G12, "<"&C3:G12)+1))=1)) + CTRL + SHIFT + ENTER

Count unique distinct values

Unique distinct values are all values but duplicates are removed.


Array formula in D19:

=SUM(--(FREQUENCY(IF((YEAR(IF(MOD(COLUMN(B3:F12), 2)=0, B3:F12))=C15), (COUNTIF(C3:G12, "<"&C3:G12)+1), ""), (COUNTIF(C3:G12, "<"&C3:G12)+1))>0)) + CTRL + SHIFT + ENTER

Download excel example file for this tutorial.

Count unique distinct values where date meets criteria in a range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

MOD(number, divisor) returns the remainder after a number is divided by a divisor

COLUMN(reference) returns the column number of a reference

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999

Adds all the numbers in a range of cells

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

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.

Related articles: