Excel doesn't have a function that counts the number of unique entries in a range, but if you want to count the number of unique entries in the range B2:B900, use this array formula: =SUM(1/COUNTIF(B2:B900,B2:B900)) and then press Ctrl+Shift+Enter. This formula will not work if there are any empty cells in the range.
- [Voiceover] In this work sheet called…Counting Unique Entries I want to know how many…different departments are in the list.…If I click on a cell and double click the bottom edge,…go down to the bottom, it's got 762 rows.…I can see a lot of repeating names in here so I'm sure…we don't have 700 different departments.…But how many do we have?…I might also want to know how many…different buildings are here.…And we could also be doing some other checks…by even counting the different names and so on.…Counting the number of unique entries is something…that you'll need from time to time and I keep waiting…for a function called Unique that would be ideal.…
About equal Unique, within parenthesis,…highlight the range where you're looking and get an answer…as to how many different entries there are.…But there is no such function.…There is a way to get to this by way of a formula though.…It's an unusual formula, it's called and Array formula…and it looks a little bit odd.…I discovered it about 20 years ago at a website…
- Prepping data for analysis
- Multiple-key sorting
- Sorting by rows or by columns
- Setting single- and multi-level subtotals
- Using text, numeric, and date filters
- Creating custom filters
- Filtering tables using slicers
- Using Advanced Filter
- Eliminating duplicate data
- Using SUMIF and COUNTIF functions for quick data analysis
- Working with the database functions such as DSUM and DMAX
Skill Level Intermediate
Working with Real-Time Data in Excelwith Chris Dutton1h 18m Intermediate
Learning Excel Data-Analysis (2015)with Curt Frye2h 24m Beginner
1. Data Preparation
2. Sorting Data
3. Creating Automatic Subtotals in Sorted Lists
4. Filtering Data
5. Advanced Filter
6. Eliminating Duplicate Data
7. Data Analysis Tools
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.