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

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.

Unique-distinct-list1

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:

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Count unique distinct values in two columns with date criteria in excel
  2. Count unique distinct records in a date range and a numeric range in excel
  3. Count unique distinct text values in a range in excel
  4. Count unique and unique distinct values in a multicolumn range in excel
  5. Count unique records by date in excel
  6. Count unique distinct values in a column in excel
  7. Count unique values and unique distinct values in three ranges combined in excel
  8. Count unique values and unique distinct values in two ranges combined
  9. Count unique distinct numbers across multiple sheets (3D range) in excel
  10. Count unique distinct months in excel