counting repeat numbers in a column

[Guest]

I have a spread sheet with two columns:

Date St. No.
4/10/06 1452
5/13/06 1339
5/22/06 3538
6/1/06 1452
6/9/06 305

In this example, I have two number “1452â€, however, my spread sheet contains
about 2000 data with numbers ranging from single digits to 5 digits. How can
I have Excel count all of the repeating numbers and providing me the answer.
I don’t want to sort the data because of other issues.

Thanks for any comments.

 

[Guest]

One quick way ... try a pivot table (PT). Select the "St. No." col., click
Data > Pivot Table ... Click Next > Next. In step 3, click Layout, then drag
n drop "St. No." into ROW & into DATA (it'll appear as Count of St. No.),
click OK > Finish. That's it. Jive over to the created PT sheet (it'll be
placed just to the left of the source sheet) for the results.

 

[Guest]

=COUNT(B2:B2000)-SUMPRODUCT((B2:B2000<>"")/COUNTIF(B2:B2000,B2:B2000))