CODEWITHSUNDEEP
excel
user955732
user955732

Reputation: 1370

Counting duplicate rows using Excel

I need to count the number of duplicate occurrences of values in a column using excel, ONLY for rows with a certain value in second column i present.

Column1   Column2
value1    x
value1    x
value1    x
value1    
value2    x
value2
value3    x

---> should give

VALUE  Occurencies
value1 3
value2 1
valu3  1

How to do this?

Thanks!

Upvotes: 1

Views: 6224

Answers (2)

teylyn
teylyn

Reputation: 35990

You can use a pivot table, which does not require any typing of values or any formulas.

Drag column1 into the row area and column2 into the values. If it is text, it will be counted. If the other values in column 2 are blank you are done. If they contain values, you can drag column2 into the filters area and then use the filter dropdown above the pivot table to select what value in column2 to use.

After you add new data to columns A and B, select the pivot table and refresh it.

enter image description here

Upvotes: 2

pnuts
pnuts

Reputation: 59495

If Column1 is in A1, please try:

=COUNTIFS(A:A,D2,B:B,"<>")

copied down to suit where D2... contain Value1 etc

Upvotes: 1

Related Questions