## Count unique distinct values within same week, month or year

*Article updated on January 16, 2018*

### Introduction

What are unique distinct values?

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

### Count unique distinct values within same week

**Formula in B3:**

**Array formula in E3:**

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

Copy cell E3 and paste it down as far as needed.

### Explaining array formula in cell E3

=SUM(--((FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))>0))

**Step 1 - Filter records in date range**

=SUM(--((FREQUENCY(**IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, "")**, Item))>0))

IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, "")

becomes

IF("2010-2"={"2010-2";"2010-2";"2010-2";"2010-2";"2011-3";"2010-3";"2010-3";"2010-3";"2010-3"}, Item, "")

becomes

IF({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}, {1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131}, "")

and returns

{1150;1150;1131;1131;"";"";"";"";""}

**Step 2 - Calculate frequency**

=SUM(--((**FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))**>0))

FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))

becomes

FREQUENCY({1150;1150;1131;1131;"";"";"";"";""}, {1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131}))

and returns

{2;0;2;0;0;0;0;0;0;0}

**Step 3 - Count and sum values larger than 0 (zero)**

=SUM(--((FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))>0))

becomes

=SUM(--(({2;0;2;0;0;0;0;0;0;0})>0))

becomes

=SUM(--({TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}))

becomes

=SUM({1;0;1;0;0;0;0;0;0;0})

and returns 2 in cell E3.

### Count unique distinct values within same month

**Array formula in E16:**

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

Copy cell E16 and paste it down as far as needed.

### Count unique distinct values within same year

**Array formula in E29:**

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

Copy cell E29 and paste it down as far as needed.

### Download excel sample file

Count-unique-occurences-within-same-week-month-year.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

**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

*.*

### 8 Responses to “Count unique distinct values within same week, month or year”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar,

You don't need the countif part, you could directly be constructing the Frequency structure, using the name range "Item"

=SUM(--(FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11,Item,""),(Item))>0))

Or am I missing something here

Yes, you are right.

But if "Item" is text (not numbers), frequency won´t be able to "count" them. COUNTIF($D$3:$D$11, "<"&$D$3:$D$11) converts all possible text values to numbers. In my example I use only numbers so my solution might seem strange. I wanted to create a more general solution if people use "Item" numbers like this: A111, A112 and so on. Thanks your contribution! /Oscar

And I now see the light! Thanks Oscar!

How then would I run or fit in the countif, if I had text that I had to look for distinct values. I have text values in item column. David

how about unique distinct values within same day? looking forward to your response.

beginner,

Array formula in cell F4:

Download excel *.xlsx file

count-unique-distinct-values-within-the-same-day.xlsx

thank you, oscar.

How Can I also count "A" and "a" two different distinct values ?