# Counting unique numbers

1. ## 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. ## 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. ## 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. ## 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. ## 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

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

#### 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