Counting distinct values based on multiple criteria

[mickowhk]

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:

2/4/2018	Tom
3/4/2018	Mary
4/4/2018	Jason
26/4/2018	Sam
27/4/2018	Tom
27/4/2018	Tom
30/4/2018	Sam
1/4/2018	Tom
1/4/2018	Tom

<colgroup><col><col></colgroup><tbody>
</tbody>

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

Matching Table:

2/4/2018

3/4/2018

4/4/2018

5/4/2018

6/4/2018 27/4/2018

<colgroup><col></colgroup><tbody>
</tbody>

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

 

[sandy666]

If you do not need a formula then you can use PowerQuery or PivotTable with DataModel

 

[mickowhk]

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.

 

[sandy666]

With PowerQuery

Date	Name		DateList		LookupName		LookupName	Count
02/04/2018​	Tom		02/04/2018​		Tom		Tom	2​
03/04/2018​	Mary		03/04/2018​
04/04/2018​	Jason		04/04/2018​
26/04/2018​	Sam		05/04/2018​
27/04/2018​	Tom		06/04/2018​
27/04/2018​	Tom		27/04/2018​
30/04/2018​	Sam
01/04/2018​	Tom
01/04/2018​	Tom

<tbody>
</tbody>

With PivotTable and DataModel

Name	Tom
DateList	Distinct Count of Date
02/04/2018​	1​
27/04/2018​	1 ​

<tbody>
</tbody>

maybe it will help: example file

 

[mickowhk]

With PowerQuery

Date	Name		DateList		LookupName		LookupName	Count
02/04/2018​	Tom		02/04/2018​		Tom		Tom	2​
03/04/2018​	Mary		03/04/2018​
04/04/2018​	Jason		04/04/2018​
26/04/2018​	Sam		05/04/2018​
27/04/2018​	Tom		06/04/2018​
27/04/2018​	Tom		27/04/2018​
30/04/2018​	Sam
01/04/2018​	Tom
01/04/2018​	Tom

<tbody>
</tbody>

With PivotTable and DataModel

Name	Tom
DateList	Distinct Count of Date
02/04/2018​	1​
27/04/2018​	1 ​

<tbody>
</tbody>

maybe it will help: example file

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)

 

[sandy666]

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

 

[mse330]

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

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>
</tbody>

Book1
	A	B	C	D	E	F	G
1	02-Apr-18	Tom		02-Apr-18		Tom	2
2	03-Apr-18	Mary		03-Apr-18
3	04-Apr-18	Jason		04-Apr-18
4	26-Apr-18	Sam		05-Apr-18
5	27-Apr-18	Tom		06-Apr-18
6	27-Apr-18	Tom		27-Apr-18
7	30-Apr-18	Sam
8
9	01-Apr-18	Tom
10	01-Apr-18	Tom
Sheet2

Cell Formulas
Range	Formula
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​