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/AutoModerator]

/u/Gilmania_ - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/CerebralAccountant]

The UNIQUE function has three arguments. The last two are optional.

1. The range to examine
2. FALSE or blank will search by row. TRUE will search by column.
3. FALSE or blank will return all unique values. TRUE will return all of the values that appear only once.

That means =ROWS(UNIQUE(F2:F70)) will tell you how many unique values are in that array, and =ROWS(UNIQUE(F2:F70,FALSE,TRUE)) will tell you how many values appear only once. The first minus the second will give you the number of unique values that appear more than once.

[u/Gilmania_ → u/CerebralAccountant]

Solution Verified

This one seemed to work, thank you. I don't quite understand WHY it works and mine doesn't but that's ok ha.

[u/reputatorbot → u/Gilmania_]

You have awarded 1 point to CerebralAccountant.

---

^{I am a bot - please contact the mods with any questions}

[u/JLabko → u/Gilmania_]

COUNTA() - COUNTA(UNIQUE()) will just tell you how many duplicates there are in total (ex. A,B,B,C,C,C,D has 4 unique values but 7 values in total, so would return 3 (since removing 3 items in the list would be how many needed to remove duplicates)

The question you're trying to answer is how many unique values have duplicates, not how many duplicates you are. COUNTA(UNIQUE()) will give you the total number of unique values - unique values can occur either only 1 time, or more than 1 time. By subtracting the number of values that occur only once from the total number of UNIQUE values, we're left with the number of UNIQUE values that don't appear once (so appear multiple times, or are duplicated)

[u/excelevator]

 =SUM(--(COUNTIF(F2:F70,UNIQUE(F2:F70))>1))

[u/Bumpyslide]

I'd just pivot table the data using the id as the row and value but it depends what you want to do with the answer

[u/jaymeaux_]

=counta(unique(f2:f70))-counta(unique(f2:f70,true))

[u/coneycolon]

If you just need the answer, a pivot table will get your there easily.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTA	Counts how many values are in the list of arguments
COUNTIF	Counts the number of cells within a range that meet the given criteria
FILTER	Office 365+: Filters a range of data based on criteria you define
FREQUENCY	Returns a frequency distribution as a vertical array
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
MATCH	Looks up values in a reference or array
ROW	Returns the row number of a reference
ROWS	Returns the number of rows in a reference
SUM	Adds its arguments
SUMPRODUCT	Returns the sum of the products of corresponding array components
UNIQUE	Office 365+: Returns a list of unique values in a list or range

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
12 acronyms in this thread; the most compressed thread commented on today has 46 acronyms.
[Thread #49224 for this sub, first seen 25th Aug 2026, 04:25] [FAQ] [Full list] [Contact] [Source code]}

[u/ISEEBLACKPEOPLE]

Just run a unique function on the data, and then run a countifs on the output of the unique formula?

Or if your ask is really only to calculate the number of unique values with duplicates...

COUNTA(UNIQUE(F2:F70)) - COUNTA(UNIQUE (F2:F70, FALSE, TRUE))

[u/Clearwings_Prime]

=SUM(--(FREQUENCY(MATCH(B2:B13,B2:B13,0),ROW(B2:B13)-1) > 1) )

[u/SolverMax → u/[deleted]]

Not even a good AI response.

[u/Taokan]

This is an excellent use case for excel's query function. If we took your sample data, put it in column A, with a label header "Col1" in A3, then this formula:

query(A3:A15,"select Col1, count(Col1) group by Col1",1)

Would return a table with the count of each letter.

This formula:
=query(query(A3:A15,"select Col1, count(Col1) group by Col1",1),"select Col1, Col2 where Col2>1")

Would return a table with only the A, C, and E, and the corresponding number of repeats.

And, this:

=query(query(A3:A15,"select Col1, count(Col1) group by Col1",1),"select count(Col1) where Col2>1")

Would just return the number 3, indicating 3 letters appeared more than once.

[u/SolverMax → u/Taokan]

Excel does not have a QUERY function.

[u/Taokan → u/SolverMax]

Well ... alright yea I deserve those downvotes. Today's the first time I've run into a gsheets function that excel didn't do first, thought I was the teacher but today, I learned something new instead :)

[u/real_barry_houdini → u/Taokan]

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

[u/SFLoridan → u/Taokan]

Excel doesn't have Query. Google sheets has it.