Only names that occur once are counted
UNIQUE’s exactly_once option removes repeated groups. That is not a distinct count.
Count distinct values and show the list
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Create the distinct list in B2:B9 and its count in E2. Ignore empty cells, keep the first spelling, and treat letter case as equivalent.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: B2, B3, B4, B5, B6, B7, B8, B9, E2. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
See which customer names are distinct, exclude blank rows, and count the resulting list.
THE QUICK ANSWER01
FILTER removes empty cells, UNIQUE keeps one copy of each distinct value, and ROWS counts them. IFERROR returns zero when every input is empty.
ƒx
=IFERROR(ROWS(UNIQUE(FILTER(A2:A9,A2:A9<>""))),0)For the example data
E2 4B2 AsterB3 BirchB4 CedarB5 DuneB6 B7 B8 B9
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Distinct is not exactly once
The default UNIQUE behavior keeps one representative of every value, including repeated ones. Its exactly_once option answers a different question: which values appear only once. Leave that option off for this task.
Blank, case and type rules
Empty cells and empty strings are excluded. Text is compared without regard to letter case, but ordinary spaces are significant. A number 7 and the text "7" are different values in this task; convert types first if your business rule should combine them.
List output and version support
The workbook uses FILTER and UNIQUE, which require a spreadsheet version that supports those functions. INDEX displays one list position per row in this small exercise. The browser, Excel and Sheets compatibility results are recorded separately; function names alone are not a compatibility guarantee.
Read the full step-by-step walkthrough
- Decide what distinct means
A name that appears three times still contributes one distinct value. We do not ask for names that appear exactly once.
- Remove empty cells first
The comparison produces a keep-or-drop condition for each row. FILTER passes only nonempty inputs onward.
FILTER(A2:A9,A2:A9<>"") - Keep one of each value
UNIQUE returns the first occurrence of each distinct value, preserving input order. Letter case alone does not make a new text value.
UNIQUE(…) - Count the resulting rows
ROWS counts the distinct list, so repeated inputs do not inflate the total.
ROWS(…) - Handle a completely empty list
When FILTER finds nothing, the count should be zero. IFERROR handles that empty-input outcome.
IFERROR(…,0) - Show the list beside the count
In B2:B9, INDEX reads the first, second and later items from the same unique list. Missing positions stay blank. In modern Excel, UNIQUE can also spill the whole list from one cell.
INDEX(UNIQUE(FILTER(…)),ROWS($B$2:B2))
Things to watch
A blank is counted as a value
Filter empty inputs before asking for unique values.
Every list row shows the first item
The INDEX position must increase as you fill down. ROWS($B$2:B2) changes from one to two and so on.
About this example
Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.
Content version 3. Practice checks compare calculated results across different datasets; they do not certify every possible formula.