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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
70
Bernie Galve
B
S
  • Question Question
Pulling Correct Number of Dates Based on Column Value
Replies
1
Views
58
dreid1011
dreid1011
U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
203
Fluff
Fluff
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
567
mrshuck
M
M
  • Solved
Index and Match
Replies
3
Views
133
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

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