Number of (text) duplicates in a column

[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).

 

[Vasant Nanavati]

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>.

 

[Gord Dibben]

quiller

Check out Chip Pearson's "highlighting duplicates and counting unique items"

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

Gord Dibben Excel MVP