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:

example table

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:

Sample Data

Put this formula into C2 and copy down all rows:




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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.