display and count the number of times a value appears

D

dbath

I am trying to count the number of times each zip code appears in an address
list and have it display each zip code with the count beside it
ie

11456 6
20457 5
74653 12
 
F

Franz Verga

Nel post *dbath* ha scritto:
I am trying to count the number of times each zip code appears in an
address list and have it display each zip code with the count beside
it
ie

11456 6
20457 5
74653 12
Click to expand...


Use the COUNTIF function:

if your zip codes are in column A starting from A2, in B2 put:

=COUNTIF(A:A;A2)

then copy down

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
D

dbath

Thank you for your help but I seem to be missing something I beleive
Maybe this will better illustrate the situation

column S column T
ZIP count

19046
20009
20016
20904
20913
21117
22041
22903
23462
23602
 
S

SteveG

If your addresses are broken out into separate cells (Street in A, City
in B and zip in C) you could use a simple Pivot Table. Assuming your
header is in
C1 and your range is C1:C100. Select the range, click on the Pivot
table wizard. When step one appears, click next. Step two should have
your range in it if you selected it before opening the wizard so click
next. Click on Layout. Your header should appear on the right. Drag
that to the Row section and then again to the data section. The item
in the data section should say "count of zip" if your header in C1 is
zip. Select where you want the Pivot Table to be and click on finish.


HTH

Steve
 
D

dbath

I got the other way to work but I will rty to learn this way as well
I quickly rear about Pivot tables and gave up but your explaination
seems fairly easy
Thank You
 
S

starguy

I suppose that you have zip codes in col A (A1:A100). enter manually zip
codes in col B only once as you shown in your question then in col C
enter following formula.

=COUNTIF($A$1:$A$100,B1) and copy it down in col C untill your data in
col B ends.

hope this will solve your problem.
 
Top