Count duplicate rows and produce count #

[CarolynR]

I have a spreadsheet of over 15,000 lines of student information, sorted by student number. I want to count the number of rows which have a duplicate student number, up to 15 duplicates in a row, and show the total number of duplicates in a Separate Column. I.e.

Column 1 Column 2
Row 1 - 200101 3
Row 2 - 200101
Row 3 - 200101
Row 4 - 200102 2
Row 5 - 200102
Row 6 - 200103 1

I've been trying to use a Countif formula, but I found I had to use so many ANDs and ORs that the formula became too long. I don't know how to use programming code, only formulas in Excel. Is there an easier solution using some type of SUMPRODUCT code?

 

[romelsms1]

=sumproduct(--(a1:a100=b1:b100),--(a1:a100<>""))

thank you, it's working...and a last request if we want to sum all the identical values?

 

[Aladin Akyurek]

thank you, it's working...and a last request if we want to sum all the identical values?

What would be the result for the sample you have in #29 ?

 

[Scott Huish]

If you are summing each duplicate value only once, i.e. 1 and 1 sums as 1 then:
=SUMPRODUCT(--(A1:A100=B1:B100),--(A1:A100<>""),A1:A100)

if it would sum as 2:
=SUMPRODUCT(--(A1:A100=B1:B100),--(A1:A100<>""),A1:A100*2)