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.

Answer:

Count unique values

Unique values are values occuring only once in a range.

Array formula in D20:

Count unique distinct values

Unique distinct values are all values but duplicates are removed.

Array formula in D19:

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

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

COUNTIF(range,criteria)
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:

• Count unique distinct values in two columns with date criteria in excel

Related posts:

Count unique distinct values in two columns with date criteria in excel

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

Count unique and unique distinct values in a multicolumn range in excel

Count unique distinct records with a date and column criteria in excel 2007

Count unique distinct values that meet multiple criteria in excel