Count and display number of items

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi there,

I have one column of telephone numbers, please can someone help me
with a formula to create another column showing how many occurrences
there are of each number. Thanks!

Andy.
 
Andy,

A pivot table can do just that. Set the data function to COUNT, if not
already.

To identify the duplicates, you can sort your table on the telephone number
column, and put in a helper column:

=if(A3=A2, "Dup","")

Copy down.
 
Assuming the numbers are in A1 through A10
={SUM((A1=$A$1:$A$10)*1)} in B1
and copy down.
It doesn't worry about duplicates in that if a number shows
up 3 times, the formula will show a 3 next to each occurrence.

{} indicates an array formula, so use Ctrl-Shift-Enter rather
than just Enter to input the formula.

ken c
 
Earl,

Thank you! I'm kind of new to Excel formulas but since you pointed me
in the right direction I got the chart I wanted after a few attempts.

One more thing - I have a second worksheet, names in one column and
there phone number in the next. Is there any way I can link the names
to the numbers so the names are shown in the pivot table rather than
the numbers?

Thanks in advance.

Andy.
 
You could add the names to you pivot source and use them instead of the
numbers,
assume the Numbers including header is in A2:A500, then create a new column
B,
name it Names and use something like

=INDEX(Sheet2!$A$2:$A$150,MATCH(A2,Sheet2!$B$2:$B$150,0))

where Sheet2!A2:A150 is the list with names and Sheet2!B2:B150 the numbers
on that second worksheet

copy down along the numbers, right click the pivot and select pivot table
wizard,
click Back and change the source range to include the newly created name
column,
click next and select layout and drag replace the numbers with the names to
get a count of names instead


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Thanks for your help.

Would it also be possible to look numbers up in Outlooks contacts?

Andy.
 
Back
Top