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

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 has two unique distinct items: 1150 and 1131.

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

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

Item is named range pointing to cell range D3:D11.

### 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.

### Download Excel *.xlsx file

Count-unique-occurences-within-same-week-month-year.xlsx

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

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

Highlight duplicates with same date, week or month

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]

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

### Leave a Reply to chrisham

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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 ?