# Counting occurrences in first column of a file in Excel

I need code to find duplicate numbers and continue to count the number of times the duplicate was found.

Example: I have 20,000 entries and 303 unique numbers. I need to know how many times each number has been duplicated. So from this:

```1 2
1 3
1 2
3 3
52 1
52 300```

and 1000s more I want to count the number of times each value occurs in the first column like so:

```1  3 (This means we saw `1` three times)
3  1
52 2```

How can I do that in Excel or should I use other software for this?

-
– pnuts May 9 '13 at 9:44

Assuming your data is in `A:B` and labelled in Row1:

`=COUNTIF(A:A,A2)`

in Row2 of another column (in example below `C`) and copied down as appropriate will count all instances. (Basically the same formula as @tdk2fe but slightly simpler and more versatile).

Then if required pivot the two columns as shown for a better view of the quantities (as suggested by @Travis with only reference to details how.)

Remove Duplicates is not wholly reliable (best avoided in my view, Data > Sort & Filter - Advanced, Unique records only etc is my preference) (ref).

(Edited to adjust image to match actual sample data whose layout was not well displayed in OP at time of previous version.)

-

I believe the best option for this would be to create a Pivot Table. If you know exactly what data you wanted to count and it was a very finite data set you could use the `COUNTIF(range, criteria)` function.

The Pivot Table in Excel has the ability to do the counts on dynamic data, meaning you aren't sure exactly what you are looking for.

The depth of pivot tables would be too great to post in an answer so please refer to this link:

http://www.dummies.com/how-to/content/the-essentials-of-excel-2010-pivot-tables-and-pivo.html

-

I would create a new column, and then use the 'Remove Duplicates' function. After that, for each value in the column, use the COUNTIF() function.

``````COUNTIF(A1:A20000, 1) <-- returns the number of times '1' shows up in column A
``````
-
This seems to imply a rather laborious hard coding of the criterion, though having extracted duplicates I doubt you intended that! Note Remove Duplicates is 'suspect'. – pnuts May 8 '13 at 15:31