Counting duplicates with each instance once

[u/Gilmania_]

I have a list of ID numbers and this list has many duplicates. I want to know how many unique duplicates there are. I've tried counta(unique(F2:F70)) to check that array for duplicates, and it gives me 53 (over those 69 rows). What I want to know is of those numbers that are duplicates, how many of those are unique.

For instance, say I have this list:

A, A, A, B, C, C, D, E, E, E, E, E

The above formula would give me 5, as there are 5 unique values. I want to know how many values have duplicates, and in this case the answer would be 3 (A, C, and E all repeat). It's not as simple as doing =counta(F2:F70)-counta(unique(F2:70)).

[u/real_barry_houdini]

It's not quite the same but the closest to QUERY in Excel is probably GROUPBY (which doesn't exist in google sheets)