Function Problems

How to Automatically List and Count Unique Names in Excel

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to automatically extract unique company names from multiple columns into a single list and count the number of times each name appears.

Product: Excel
Device & OS: not provided
Scenario: Organizing and analyzing repetitive data spread across multiple destination columns.
Observed behavior: New entries in any of the specified destination columns should automatically populate a unique list and dynamically update their corresponding occurrence counts.

Before you start

Ensure your version of Excel supports dynamic array functions like UNIQUE and TOCOL, such as Microsoft 365, Excel 2021, or the latest version of WPS Office Spreadsheet.

Solution 1Recommended

Use UNIQUE and TOCOL Functions to Extract and Count Names

This is the most direct method to flatten multiple columns into a single unique list and dynamically count their occurrences.

The TOCOL function takes data from multiple columns and flattens it into a single column. Wrapping this in the UNIQUE function instantly filters out duplicates. Finally, COUNTIF with a spilled range reference calculates the frequencies.

Generate the unique list

Select the cell where you want the unique list to appear (e.g., E2) and enter the formula =UNIQUE(TOCOL(A2:C6, 3)). The '3' in TOCOL tells Excel to ignore blanks and errors.

Apply the array formula

Press Enter to generate the dynamic list of unique company names.

Set up the dynamic count

Select the adjacent cell for the count (e.g., F2) and enter the formula =COUNTIF($A$2:$C$6, E2#). The '#' symbol ensures the count automatically applies to the entire dynamic array spilled from cell E2.

Update data

Test the setup by adding a new company name into your source range (A2:C6). The unique list and counts will automatically update.

Automatic Updates: Using the spilled range operator (#) guarantees that your COUNTIF formula will expand or shrink automatically as the unique list changes.

Solution 2

Use LET and FILTER Functions for Advanced Blank Handling

An alternative approach if you need more explicit control over filtering out empty cells in your dataset using the LET and FILTER functions.

Efficient Data Analysis with WPS Office

Extract and Count Unique Values Easily in WPS Spreadsheet

WPS Office Spreadsheet fully supports advanced dynamic array functions like UNIQUE, TOCOL, and FILTER, allowing you to seamlessly process complex datasets without paying for expensive subscriptions.

1. Open your data: Launch WPS Spreadsheet and open the workbook containing your company names.
2. Extract unique values: Type =UNIQUE(TOCOL(range, 3)) into your target cell to instantly extract distinct entries from multiple columns.
3. Count the occurrences: Use =COUNTIF(range, target_cell#) in the adjacent column to count occurrences automatically as the list updates.

Fully compatible with Microsoft Excel formulas, functions, and file formats.Supports modern dynamic arrays (UNIQUE, FILTER, TOCOL) for automated data processing.Free and lightweight alternative for powerful everyday spreadsheet tasks.Clean, intuitive interface with built-in data analysis tools.

Frequently Asked Questions

Why am I getting a #NAME? error when using TOCOL or UNIQUE?

The #NAME? error usually occurs if you are using an older version of Excel (like Excel 2016 or 2019) that does not support modern dynamic array functions. You need Microsoft 365, Excel 2021, or a modern alternative like WPS Office Spreadsheet to use these formulas.

What does the '#' symbol mean in the COUNTIF formula?

The '#' symbol is known as a spilled range operator. It tells the COUNTIF function to reference the entire dynamic array created by the UNIQUE function. This means the count column will automatically expand or contract as new unique names are added or removed.

How can I automatically include new rows added to the bottom of my data?

To make your source range automatically expand when new rows are added, convert your source data into an Excel Table (press Ctrl + T) and use structured references (e.g., Table1[Column1]) in your TOCOL and COUNTIF formulas instead of static ranges like A2:C6.

Can I automatically sort the extracted unique list alphabetically?

Yes, you can easily sort the list by wrapping the SORT function around your existing formula. For example, entering =SORT(UNIQUE(TOCOL(A2:C6, 3))) will return the unique company names in A-Z alphabetical order.

Excel & Data Problems