Counting distinct values based on multiple criteria

M

mickowhk

New Member
Joined
Aug 10, 2018
Messages
3
There are two columns in the data table, one includes the date and another one includes name like the one shown below. There is also a data table, which is a list of date. I would like to pick a name, let's say Tom, and count the no. of dates corresponding to Tom in the dates listed in the data table. To be specific, Tom appears on 2/4 for one time, 27/4 for two times, 1/4 for two times. However, 1/4 is not on the list, so it is not counted. There are two distinct dates left which are 2/4 and 27/4, so the outcome should be 2. I would really appreciate if someone can help me to figure out the formula, I have spent hours on it, thanks!!!

Data Table:

[TABLE="width: 123"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]2/4/2018[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]3/4/2018[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]4/4/2018[/TD]
[TD]Jason[/TD]
[/TR]
[TR]
[TD]26/4/2018[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]27/4/2018[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]27/4/2018[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]30/4/2018[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/4/2018[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]1/4/2018[/TD]
[TD]Tom[/TD]
[/TR]
</tbody>[/TABLE]

P.S. There are blank rows in between the table, which can be deleted if necessary.

Matching Table:

[TABLE="width: 74"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2/4/2018[/TD]
[/TR]
[TR]
[TD]3/4/2018[/TD]
[/TR]
[TR]
[TD]4/4/2018[/TD]
[/TR]
[TR]
[TD]5/4/2018[/TD]
[/TR]
[TR]
[TD]6/4/2018
27/4/2018[/TD]
[/TR]
</tbody>[/TABLE]

Meet 2 criteria:
1. the date listed on the matching table
2. The assigned name
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
If you do not need a formula then you can use PowerQuery or PivotTable with DataModel
 
Upvote 0
Thank you for your reply, I have tried to use the Data model to create distinct date count. It appears some of the counts are over 31 which doesn't make sense. I have no idea on that so I am looking for a workable formula.
 
Upvote 0
With PowerQuery

[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #5B9BD5"]Date[/TD]
[TD="bgcolor: #5B9BD5"]Name[/TD]
[TD][/TD]
[TD="bgcolor: #5B9BD5"]DateList[/TD]
[TD][/TD]
[TD="bgcolor: #5B9BD5"]LookupName[/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"]LookupName[/TD]
[TD="bgcolor: #70AD47"]Count[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
02/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]
02/04/2018​
[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Tom[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[/TR]
[TR]
[TD]
03/04/2018​
[/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD]
03/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
04/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Jason[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]
04/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
26/04/2018​
[/TD]
[TD]Sam[/TD]
[TD][/TD]
[TD]
05/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
27/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]
06/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
27/04/2018​
[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD]
27/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
30/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Sam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
01/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
01/04/2018​
[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


With PivotTable and DataModel

[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #DDEBF7"]Name[/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]DateList[/TD]
[TD="bgcolor: #DDEBF7"]Distinct Count of Date[/TD]
[/TR]
[TR]
[TD]
02/04/2018​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
27/04/2018​
[/TD]
[TD]
1
​
[/TD]
[/TR]
</tbody>[/TABLE]


maybe it will help: example file
 
Last edited:
Upvote 0
sandy666 said:
With PowerQuery

[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #5B9BD5"]Date[/TD]
[TD="bgcolor: #5B9BD5"]Name[/TD]
[TD][/TD]
[TD="bgcolor: #5B9BD5"]DateList[/TD]
[TD][/TD]
[TD="bgcolor: #5B9BD5"]LookupName[/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"]LookupName[/TD]
[TD="bgcolor: #70AD47"]Count[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
02/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]
02/04/2018​
[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Tom[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[/TR]
[TR]
[TD]
03/04/2018​
[/TD]
[TD]Mary[/TD]
[TD][/TD]
[TD]
03/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
04/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Jason[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]
04/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
26/04/2018​
[/TD]
[TD]Sam[/TD]
[TD][/TD]
[TD]
05/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
27/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]
06/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
27/04/2018​
[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD]
27/04/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
30/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Sam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]
01/04/2018​
[/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
01/04/2018​
[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


With PivotTable and DataModel

[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #DDEBF7"]Name[/TD]
[TD="bgcolor: #DDEBF7"]Tom[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]DateList[/TD]
[TD="bgcolor: #DDEBF7"]Distinct Count of Date[/TD]
[/TR]
[TR]
[TD]
02/04/2018​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]
27/04/2018​
[/TD]
[TD]
1
​
[/TD]
[/TR]
</tbody>[/TABLE]


maybe it will help: example file
Click to expand...

This helps a lot, thank for your detailed explanation, I think it should work. I am sorry but I am an idiot in excel, may I know how to include multiple tables in a single pivot table? I did many research on that, but still can't figure out. My major problem is that I can't see my table (Datelist) on the pivot table pannel "all" tab, I can only see the table I used for creating the pivot table (The one with Date and Name)
 
Upvote 0
add each table to DataModel (PowerPivot tab), create appropriate relationships and...
now two ways:
- ribbon - Insert - PivotTable - Use this workbook's DataModel - ...
or
- PowerPivot tab - Manage - (ribbon) PivotTable - PivotTable
 
Upvote 0
Just an alternative way to acheive the same result with a UDF (User Defined Function). The function has 3 arguments which are your data range, the dates range & the 3rd one is the Name. You can add it to your workbook by following the below steps:

1. Press Alt+F11
2. Press Alt+I+M
3. Paste the below code


Code: 
Function Count_Distinct(DataRg As Range, LookUpDateRg As Range, Name As String) As Long
Dim Dic As Object, Ar1() As Variant, Ar2() As Variant
Set Dic = CreateObject("Scripting.Dictionary")
Ar1 = DataRg
Ar2 = LookUpDateRg
For x = LBound(Ar1) To UBound(Ar1)
    If Not Dic.exists(Ar1(x, 1)) And Ar1(x, 2) = Name Then
        Dic.Add Ar1(x, 1), Ar1(x, 2)
    End If
Next x
For x = LBound(Ar2) To UBound(Ar2)
    If Dic.exists(Ar2(x, 1)) And Dic(Ar2(x, 1)) = Name Then
       Count_Distinct = Count_Distinct + 1
    End If
Next x
End Function


<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>


Book1
ABCDEFG
102-Apr-18Tom02-Apr-18Tom2
203-Apr-18Mary03-Apr-18
304-Apr-18Jason04-Apr-18
426-Apr-18Sam05-Apr-18
527-Apr-18Tom06-Apr-18
627-Apr-18Tom27-Apr-18
730-Apr-18Sam
8
901-Apr-18Tom
1001-Apr-18Tom
Sheet2
Cell Formulas
RangeFormula
G1=Count_Distinct(A1:B10,D1:D6,F1)



Just couple of important remarks:
1. You must save your workbook with .xlsm or .xlsb format for the UDF to work
2. Your data range is structured to have the date then the name in adjacent columns. If it differs, let me know & I will adjust the code accordingly​
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
57
Bernie Galve
B
U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
189
Fluff
Fluff
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
540
mrshuck
M
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
569
Domenic
D
A
  • Solved
COUNT multiple SUM COUNTIFS
Replies
6
Views
141
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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