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

date		date	Distinct Count of date
31/01/2019​		31/01/2019​	1​
31/01/2019​		28/02/2019​	1​
31/01/2019​		31/03/2019​	1​
28/02/2019​		Grand Total	3​
28/02/2019​