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:

=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 merged into one distinct value.

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

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:

1. Count unique distinct records in a date range and a numeric range in excel
2. Count unique distinct values within same week, month or year in excel
3. Count unique distinct text values in a range in excel
4. How to count unique distinct occurrences for each date in excel
5. Count unique records by date in excel
6. Count unique distinct numbers across multiple sheets (3D range) in excel
7. Count unique distinct months in excel
8. Count unique text values in a range containing both numerical and text values
9. Count date records between two dates in a range in excel
10. Create unique distinct year and months from a long date listing in excel