Number of (text) duplicates in a column

  • Thread starter Thread starter quiller
  • Start date Start date
Q

quiller

I have a spreadsheet that lists all the movies in my dorm building by
name, format and who owns them. Obviously, many people have the same
movies throughout the building, but I am looking to get some statistics
on the list, such as:

- How many unique movies
- How many unique rooms/people involved
- How many DVD/VHS movies

It would seem the same concept can be applied to all three. I need a
formula that will take a column (or just A2:A9999) and find how many
duplicates there are, then I can subtract that from the total number of
records (non-blank cells).
 
If there are no blanks, use the infamous <g> Dave Hager formula to count the
number of unique items is a range:

=SUM(1/COUNTIF(A2:A9999,A2:A9999))

entered as an array formula with <Ctrl> <Shift> <Enter>.
 
Back
Top