+ Reply to Thread
Results 1 to 5 of 5

Counting unique numbers

  1. #1
    Registered User
    Join Date
    04-15-2009
    Location
    Cleveland, OH
    MS-Off Ver
    Excel 2003
    Posts
    2

    Counting unique numbers

    I have my cell phone bill in spreadsheet format. I want to count the number of times each phone number appears on the bill. So, I want to count the number of times that 123.456.7890 appears on the bill, the number of times 123.456.0987 shows up and so on. I have several phones on my account, so the spreadsheet is rather large (over 2000 lines). What I am trying to avoid is going through the entire spreadsheet and locating each unique number manually.

    Thanks for any suggestions.

    --Brian

  2. #2
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: Counting unique numbers

    Try this COUNTIF function in cell C1:

    =COUNTIF(A1:A2000,B1)

    Assuming the phone number range is A1 to A2000, enter the number you are wanting to count in cell B1

  3. #3
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

    Re: Counting unique numbers

    Hi Maxnpj,

    To summarize your data and get counts, try this:

    1. Select column A (your list of phone numbers), then click Data -> Filter -> Advanced Filter. If you get a message stating that Excel can't find the column header, click OK.

    2. In the Advanced Filter pop-up the List Fill Range should automatically default to the used rows in the column you selected (e.g. A1:A2000). Check the box labeled "Copy to another location" and in the "Copy to" textbox, type the cell reference B1 (or C1, D1, any blank column you're not using). Finally, check "Unique records only" and click OK. You'll then have a list of all the unique phone numbers.

    3. Assuming your unique list is in B1:B???, in C1 put the formula:

    =COUNTIF($A:$A,B1)

    4. Fill that down column C as many rows as there is data in column B (if you hover over the lower right corner of C1 the mouse cursor will become a cross - just double-click on that spot and Excel will auto-fill the formula down for you).

    Hope that helps!

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Counting unique numbers

    or perhaps better yet, use a Pivot Table, set your Number field as both Row Field and Data Field (set to COUNT).

  5. #5
    Registered User
    Join Date
    04-15-2009
    Location
    Cleveland, OH
    MS-Off Ver
    Excel 2003
    Posts
    2

    Thumbs up Re: Counting unique numbers

    Paul;
    Thanks for the response, that worked perfect. It's exactly what I was looking for.

    DonkeyOte;
    I'm pretty bad at pivot tables, although I'm going to try your suggestion since you told me *what* to do instead of merely suggesting a pivot table.

    ncmay;
    Thanks for the suggestion, but using only COUNTIF would require me to look at the spreadsheet to determine each unique number, but as I said, thanks for the response and the suggestion.

    So to all of you, thank you.

    --maxnpj

+ 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