CODEWITHSUNDEEP
excelduplicatesexcel-formula
user2283598
user2283598

Reputation: 13

Counting duplicate values

I am trying to return the number of times a duplicate value occurs in a column in excel

For instance:

Column A  | Column B
12345678  | Return 1
12345678  | Return 2
12345678  | Return 3
23456789  | Return 1
23456789  | Return 2
34567891  | Return 1

I should have made my example better, this would be the the dupes are lumped together. In my case they are not. 

Column A | Column B 

12345678 | Return 1 
23456789 | Return 1 
12345678 | Return 2 
23456789 | Return 2 
34567891 | Return 1 
12345678 | Return 3

Upvotes: 1

Views: 1080

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Put this in Cell B1 and copy it down

=COUNTIF($A$1:$A1,A1)

Explanation:

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify.

If you note that I have kept the first Range constant but affixing $'s $A$1 and kept the 2nd range as dynamic. As you copy down, the first range will remain a constant and the formula will update to accomodate the left most col A Cell.

Screenshot (For both Scenarios)

enter image description here

Upvotes: 1

Related Questions