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

### Introduction

What is 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

*.*

### Category: Count values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: Table of Contents Count […]

Comments(26) Filed in category: Count values, Excel, Unique distinct values, Unique values

### Category: Dates

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]

Comments(142) Filed in category: Dates, Excel

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Comments(82) Filed in category: Dates, Excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Comments(48) Filed in category: Dates, Excel

### Category: Frequency

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]

Comments(5) Filed in category: Excel, Frequency, Mod

### Category: Sum

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]

Comments(48) Filed in category: Combinations, Excel, Solver, Sum

In this post, I will provide a formula to sum values in column (Qty) where an column (Date) meets two […]

Comments(27) Filed in category: Excel, Sum

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is […]

Comments(19) Filed in category: Excel, Sum

### Category: Unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Comments(78) Filed in category: Excel, Unique distinct values

### Category: Year

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

### Leave a Reply

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

<code>your formula</code>

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