Counting occurrences in first column of a file in Excel

Posted on


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?


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


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.)

SU593099 example

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:

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

Leave a Reply

Your email address will not be published. Required fields are marked *