Count Unique Values in a list based on criteria

Will85

Board Regular
Joined
Apr 26, 2012
Messages
240
Office Version
  1. 365
Platform
  1. Windows
Column A has an Employee Name
Column B has a Date

An employee name can repeat on the same date

I want to know by month the sum of the count of the number of unique names each day.


John Doe 4/2/2019
John Doe 4/2/2019
Jack Smith 4/2/2019
John Doe 5/1/2019
Jack Smith 5/19/2019
Jack Smith 5/19/2019
Jack Smith 5/19/2019
Jack Smith 5/19/2019
Jack Smith 5/30/2019

Desired Result

April=2
May=3
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Also, I think your May count should be 2 in your example.

Drag formula down as needed.
Excel Workbook
AB
1NameDate
2John Doe4/2/2019
3John Doe4/2/2019
4Jack Smith4/2/2019
5John Doe5/1/2019
6Jack Smith5/19/2019
7Jack Smith5/19/2019
8Jack Smith5/19/2019
9Jack Smith5/19/2019
10Jack Smith5/30/2019
11
12April2
13May2
Sheet
 
Upvote 0
It should be 3, John came on the 1st, Jack on the 19th, and Jack again on the 30th

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Also, I think your May count should be 2 in your example.

Drag formula down as needed.

AB
1NameDate
2John Doe4/2/2019
3John Doe4/2/2019
4Jack Smith4/2/2019
5John Doe5/1/2019
6Jack Smith5/19/2019
7Jack Smith5/19/2019
8Jack Smith5/19/2019
9Jack Smith5/19/2019
10Jack Smith5/30/2019
11
12April2
13May2

<colgroup><col style="width:30px; "><col style="width:82px;"><col style="width:91px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B12{=SUM(IF(FREQUENCY(IF(TEXT($B$2:$B$10,"mmmm")=$A12,MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($B$2:$B$10)-ROW($B$2)+1),1))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
Sorry, didn't catch the per day.
Try this which is also an array formula that must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
AB
1NameDate
2John Doe4/2/2019
3John Doe4/2/2019
4Jack Smith4/2/2019
5John Doe5/1/2019
6Jack Smith5/19/2019
7Jack Smith5/19/2019
8Jack Smith5/19/2019
9Jack Smith5/19/2019
10Jack Smith5/30/2019
11
12April2
13May3
Sheet
 
Upvote 0
with Power Query

NameDateMonth NameCount
John Doe
02/04/2019​
April
2​
John Doe
02/04/2019​
May
3​
Jack Smith
02/04/2019​
John Doe
01/05/2019​
Jack Smith
19/05/2019​
Jack Smith
19/05/2019​
Jack Smith
19/05/2019​
Jack Smith
19/05/2019​
Jack Smith
30/05/2019​

Code:
[SIZE=1]// Table12
let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    Month = Table.AddColumn(Type, "Month Name", each Date.MonthName([Date]), type text),
    Day = Table.AddColumn(Month, "Day Name", each Date.DayOfWeekName([Date]), type text),
    Group = Table.Group(Day, {"Month Name"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    Group[/SIZE]
 
Last edited:
Upvote 0
Maybe

B12 copied down
=SUM(IF(FREQUENCY(IF(TEXT(B$2:B$10,"mmmm")=A12,MATCH(A$2:A$10&"|"&B$2:B$10,A$2:A$10&"|"&B$2:B$10,0)),ROW(A$2:A$10)-ROW(A$2)+1),1))
Ctrl+Shift+Enter

M.
 
Upvote 0
How bout "regular" formula

Code:
<br /><table border="1" cellspacing="0"  style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px;  " /><col style="width:76.04px;" /><col style="width:98.85px;" /><col style="width:19.96px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">NAME</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DATE</td><td style="background-color:#92d050; font-weight:bold; "> </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Month</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">UNIQUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >John</td><td style="text-align:right; ">04/02/2019</td><td > </td><td style="text-align:center; ">April</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >John</td><td style="text-align:right; ">04/02/2019</td><td > </td><td style="text-align:center; ">May</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Jack</td><td style="text-align:right; ">04/02/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >John</td><td style="text-align:right; ">05/01/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Jack</td><td style="text-align:right; ">05/19/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Jack</td><td style="text-align:right; ">05/19/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Jack</td><td style="text-align:right; ">05/19/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Jack</td><td style="text-align:right; ">05/19/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Jack</td><td style="text-align:right; ">05/30/2019</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=SUMPRODUCT((TEXT($B$2:$B$10,"mmmm")=D2)*(MATCH($A$2:$A$10&$B$2:$B$10, $A$2:$A$10&$B$2:$B$10,0)=ROW($B$2:$B$10)-1))</td></tr></table></td></tr></table>
 
Upvote 0
I added a helper column and copied down:

=IF((COUNTIFS(A$1:A1, A1, B$1:B1, B1)/COUNTIFS(A:A, A1, B:B, B1))=1, 1, 0)

Then I sum'd the helper column if I matched the date range in column B
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top