Count Distinct on Multiple columns with criteria

[B0ns41]

Hi all,

Hi have a table with three columns, one for names (column A) and the other two for dates (Column B and C). I need to count the distinct dates from column B and C based on criteria on column A.
For example, if the criteria is Paul the result must be 4 (count of distinct dates for this name in both columns, B and C).

How can I do this with formulas?

Thanks.


[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD="align: center"]Date1[/TD]
[TD="align: center"]Date2[/TD]
[TD="align: center"]Date1:Date2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Paul[/TD]
[TD]12-01-2018[/TD]
[TD]12-01-2018[/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Paul[/TD]
[TD]12-01-2018[/TD]
[TD]12-01-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]13-01-2018[/TD]
[TD]12-01-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]12-01-2018[/TD]
[TD="align: center"]12-01-2018[/TD]
[TD="align: center"]12-01-2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Laura[/TD]
[TD]13-01-2018[/TD]
[TD]14-01-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]15-01-2018[/TD]
[TD="align: center"]16-01-2018[/TD]
[TD="align: center"]15-01-2018[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD]14-01-2018[/TD]
[TD]12-01-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]17-01-2018[/TD]
[TD="align: center"]16-01-2018[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Paul[/TD]
[TD]15-01-2018[/TD]
[TD]16-01-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]17-01-2018[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Thomas[/TD]
[TD]15-01-2018[/TD]
[TD]12-01-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Paul[/TD]
[TD]15-01-2018[/TD]
[TD]17-01-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Laura[/TD]
[TD]15-01-2018[/TD]
[TD]18-01-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

With the formulas, you can report the number of unique dates for each Name.

Book1
	F	G	H
2	2	3	4
Sheet58

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF(A2:A10=E2,B2:B10),IF(A2:A10=E2,B2:B10))>0,1))}
G2	{=SUM(IF(FREQUENCY(IF(A2:A10=E2,C2:C10),IF(A2:A10=E2,C2:C10))>0,1))}
H2	{=SUM(IF(FREQUENCY(IF(A2:A10=E2,B2:C10),IF(A2:A10=E2,B2:C10))>0,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[B0ns41]

With the formulas, you can report the number of unique dates for each Name.

	F	G	H

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]4[/TD]

</tbody>

Sheet58​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(A2:A10=E2,B2:B10),IF(A2:A10=E2,B2:B10))>0,1))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(A2:A10=E2,C2:C10),IF(A2:A10=E2,C2:C10))>0,1))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(A2:A10=E2,B2:C10),IF(A2:A10=E2,B2:C10))>0,1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

What I need is to report the number of unique dates in column B and C as if they were one column (one range), not the the total of unique dates in each column...

 

[DRSteele]

What H2 does is report the number of unique dates in columns B and C.