Excel: List Distinct Names and Count Each Name
Question details
The user needs to generate a sorted list of unique names and calculate the occurrence count for each name.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A source range is stored under the named range Line, and the user wants the distinct names and their counts to spill into a two-column result.
- Observed behavior
- The desired output is a dynamic list with each distinct name in the first column and its corresponding count in the second column.
Confirm that the source cells are assigned the named range Line and that the output area has enough empty cells for the spilled two-column result. Use a version of Excel or WPS Spreadsheets that supports LET, SORT, UNIQUE, COUNTIFS, and HSTACK.
Use a dynamic-array formula to list and count distinct names
Use this solution when you want a sorted, automatically updating two-column list from the named range Line.
The formula first creates a sorted unique-name array, counts each name against the source range, and combines both arrays horizontally. The result expands or contracts automatically when the source data changes.
Open the worksheet and click the blank cell where the distinct-name list should begin, such as A2.
In the selected cell, enter =LET(distinctLine,SORT(UNIQUE(Line)),count,COUNTIFS(Line,distinctLine),HSTACK(distinctLine,count)) and press Enter.
Check the two columns that spill from the formula: the first column contains sorted distinct names, and the second contains the number of occurrences for each name.
Create a distinct-name count with a PivotTable
Use a PivotTable when dynamic-array functions are unavailable or when you need additional grouping and filtering options.
Create sorted distinct-name counts in WPS Spreadsheets
WPS Spreadsheets can use the same dynamic-array approach to return a sorted list of unique names and their counts in adjacent columns. Its familiar spreadsheet interface and Microsoft Excel format compatibility make it easy to open, edit, and save the workbook.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheet, and open the workbook containing the named range Line.
- 2. Choose an empty output cell: Click the cell where the result should start and make sure the cells to the right and below are empty.
- 3. Enter the formula: Type =LET(distinctLine,SORT(UNIQUE(Line)),count,COUNTIFS(Line,distinctLine),HSTACK(distinctLine,count)) in the formula bar or selected cell, then press Enter.
- 4. Check the two-column result: Review the spilled output for the sorted distinct names and their counts, then save the workbook as an XLSX file if needed.
Frequently Asked Questions
What does the Excel formula for distinct names and counts return?
=LET(distinctLine,SORT(UNIQUE(Line)),count,COUNTIFS(Line,distinctLine),HSTACK(distinctLine,count)) returns a sorted unique-name list in the first column and the number of times each name appears in Line in the second column.
What should I do if Excel shows a #NAME? error?
Check whether your Excel version supports LET, UNIQUE, SORT, and HSTACK. If HSTACK is unavailable, use a supported Excel version, WPS Spreadsheets with compatible dynamic-array functions, or create the result with a PivotTable.
Why does the formula return a spill error?
A spill error usually means that cells in the intended result area are not empty. Select the spill area, remove existing values or merged cells, and enter the formula again.
Can I use a cell range instead of the named range Line?
Yes. Replace every instance of Line with a range such as $A$2:$A$500, for example =LET(distinctNames,SORT(UNIQUE($A$2:$A$500)),counts,COUNTIFS($A$2:$A$500,distinctNames),HSTACK(distinctNames,counts)).
How can I count names without sorting them?
Remove the SORT function and use =LET(distinctNames,UNIQUE(Line),counts,COUNTIFS(Line,distinctNames),HSTACK(distinctNames,counts)). The names will remain in the order returned by UNIQUE.
