# How to filter rows in Excel 2016 that have count greater than 1?

Read carefully, it might get complicated.But i will try to be at the point as much as possible.

I have an excel workbook of some thousand rows. Now i have to figure out the and display the rows that have count greater than 1 and then how many times each row is repeated.

for example:

Now in the above example table there are different values for column B and the values are repeated and some are not. So I wanna filter the rows that have count greater than 1 ( for eg. " In progress"). So now, we have only rows that have count greater than 1.

Now i wanna Know how many times each row is repeated? like ; " In progress" is repeated 2 times.

It would have been easy if I only had 10 or 15 rows, But like i said I have thousands of rows with hundreds of different values.

So, how to accomplish it?

• Is col A relevant? i.e., are you looking for counts of each unique col A value with multiples of the same status, or just counts of status values? If the later, there are only a few status values. Do you want to repeat the counts on every row? Check out pivot tables. That will give you either with a few mouse clicks (but in a separate display, not as an adjacent column). – fixer1234 Dec 5 '16 at 22:17
• @fixer, no col A is not relevant – RaJ Dec 6 '16 at 1:23

Example data:

Put this formula into `C2` and copy down all rows:

`=COUNTIF(\$B\$2:\$B\$18,B2)`

Result:

Filter on column C for anything greater than 1 to satisfy your requirement.

• great thanks, it works good – RaJ Dec 6 '16 at 2:44
• ok. so here is another issue. Now how can i filter Unique values in col B avoiding repetitions. so that my output will be like – RaJ Dec 6 '16 at 3:07
• Now is there any way that i can filter for Unique values in col B avoiding repetitions. For Eg. above table has unique values like Resolved, Not Resolved, In progress and Assigned to developer – RaJ Dec 6 '16 at 3:42
• You could copy the entire column B, then use Data -> Remove Duplicates to get a distinct list. You can then use the same formula to get the counts (referencing the original column B as the first parameter to the `COUNTIF` and the relevant cell in your new distinct list as the second parameter). Alternatively, a Pivot Table would provide the same functionality if you'd prefer. – 3N1GM4 Dec 6 '16 at 8:33