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.
Get 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.
Get excel sample file
Count-unique-occurences-within-same-week-month-year.xls
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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 […]
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 press with left mouse […]
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 […]
How to count unique distinct values based on a date
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
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 Oscar
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.
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:
Get the 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 ?