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.

	A	B	C	D	E	F	G	H
1	Name	Date1	Date2		Name	Date1	Date2	Date1:Date2
2	Paul	12-01-2018	12-01-2018		Paul	2	3	4
3	Paul	12-01-2018	12-01-2018
4	John	13-01-2018	12-01-2018			12-01-2018	12-01-2018	12-01-2018
5	Laura	13-01-2018	14-01-2018			15-01-2018	16-01-2018	15-01-2018
6	John	14-01-2018	12-01-2018				17-01-2018	16-01-2018
7	Paul	15-01-2018	16-01-2018					17-01-2018
8	Thomas	15-01-2018	12-01-2018
9	Paul	15-01-2018	17-01-2018
10	Laura	15-01-2018	18-01-2018

<tbody>
</tbody>

 

[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
2	2	3	4

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet58​

Array Formulas Cell 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))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

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.