+ Reply to Thread
Results 1 to 3 of 3

Counting Unique Strings In A Column

  1. #1
    Registered User
    Join Date
    10-17-2011
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    8

    Counting Unique Strings In A Column

    I have a database in which one column contains IP addresses.

    Sometimes, an IP address appears more than once in the column.

    I need a function that counts the amount of unique IP addresses in that column.

    (ie.
    With a data set of:

    A1: 123.45.67.89
    A2: 123.45.67.89
    A3: 987.65.43.21

    the function would return 2
    )

    Can anyone enlighten me?

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Counting Unique Strings In A Column

    try this:

    Please Login or Register  to view this content.
    Last edited by tom1977; 11-23-2011 at 06:52 PM.
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Counting Unique Strings In A Column

    Hello Maxc, try this,

    =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

    Do not use whole column reference lie A:A. Use a dynamic range or limit to used range.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1