Author: Oscar Cronquist Article last updated on October 11, 2018

Question: How to count unique distinct values on the same date?


Array formula in D3:

=SUM(--(FREQUENCY(IF(B3=Date, COUNTIF(Item, "<"&Item), ""), (COUNTIF(Item, "<"&Item)))>0)) + CTRL + SHIFT + ENTER.

Copy cell and paste it down as far as needed.

Named ranges

Date (B3:B11)
Item (C3:C11)
What is named ranges?

What is unique distinct values?

Unique distinct values are all values but duplicates are merged into one value.

Download excel sample file

Count unique occurence from each date.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

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.