Distinct Count

[Excel515]

So I have multiple month-end dates in a column. I have the below formula which counts the number of distinct dates and that works, but as I add more month-ends throughout the year I keep having to adjust the below formula. I tried doing =SUMPRODUCT(1/COUNTIF(B:B,B:B)) but that seems to break it. So I was just wondering if there is a better way to get that distinct count and make the formula dynamic so to speak? Any help would be much appreciated


Example Formula currently: =SUMPRODUCT(1/COUNTIF($B$2:$B$471,$B$2:$B$471))
Column B
1/31/2019
1/31/2019
1/31/2019
2/28/2019
2/28/2019
3/31/2019

 

[sandy666]

maybe use Count Distinct in PivotTable with Data Model

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]date[/td][td][/td][td=bgcolor:#DDEBF7]date[/td][td=bgcolor:#DDEBF7]Distinct Count of date[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

31/01/2019​

[/td][td][/td][td]

31/01/2019​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

31/01/2019​

[/td][td][/td][td]

28/02/2019​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

31/01/2019​

[/td][td][/td][td]

31/03/2019​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

28/02/2019​

[/td][td][/td][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

28/02/2019​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]