Count Unique Values in a list based on criteria

W

Will85

Active Member
Joined
Apr 26, 2012
Messages
254
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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

AhoyNC said:
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
John Doe
John Doe
Jack Smith
John Doe
Jack Smith
Jack Smith
Jack Smith
Jack Smith
Jack Smith

<colgroup><col style="width:30px; "><col style="width:82px;"><col style="width:91px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Date[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]4/2/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]4/2/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]4/2/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]5/1/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]5/19/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]5/19/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]5/19/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]5/19/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]5/30/2019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]April[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]May[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2[/TD]

</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
Click to expand...
 
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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Date[/td][td][/td][td=bgcolor:#70AD47]Month Name[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]John Doe[/td][td=bgcolor:#DDEBF7]
02/04/2019​
[/td][td][/td][td=bgcolor:#E2EFDA]April[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]John Doe[/td][td]
02/04/2019​
[/td][td][/td][td]May[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jack Smith[/td][td=bgcolor:#DDEBF7]
02/04/2019​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]John Doe[/td][td]
01/05/2019​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jack Smith[/td][td=bgcolor:#DDEBF7]
19/05/2019​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jack Smith[/td][td]
19/05/2019​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jack Smith[/td][td=bgcolor:#DDEBF7]
19/05/2019​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jack Smith[/td][td]
19/05/2019​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jack Smith[/td][td=bgcolor:#DDEBF7]
30/05/2019​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
You must log in or register to reply here.

Similar threads

B
  • Question Question
VBA to show and change list of names depending on selection
Replies
7
Views
736
mumps
mumps
B
  • Solved
Dynamic Filter by row
Replies
5
Views
695
BrettJH
B
BrettOlbrys1
  • Solved
Count Unique Values Based on Multiple Criteria
Replies
6
Views
463
BrettOlbrys1
BrettOlbrys1
E
  • Question Question
Rolling Count
Replies
1
Views
468
awoohaw
awoohaw
I
  • Question Question
Tracking vacation requests
Replies
9
Views
943
KRice
K

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

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