Forum Discussion

DamHall's avatar
DamHall
Copper Contributor
Dec 13, 2023
Solved

Frequency of Unique phone numbers (11 digits)

I have a list of 708 rows of phone numbers with many duplicates. How do I determine how many unique phone numbers there are? In other words, how many different phone numbers texted me?
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Dec 13, 2023

    DamHall 

    =SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))

     

    This returns the count of unique numbers in my example.

Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 13, 2023

DamHall 

 

Alternatively if you run Excel 2021 or 365

 

with previous versions there's also:

=SUM(
  SIGN(
    FREQUENCY(Table1[Phone], Table1[Phone])
  )
)

 

=COUNT( UNIQUE(Table[Phone]) )

 

Resources