How to Count Unique Company Names Across Excel Sheets in WPS Office
Question details
The user needs to count distinct company names that appear in a Company Name column across an 18-sheet Excel workbook.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains 18 sheets, each with a Company Name column. The user wants a single distinct count without manually comparing lists sheet by sheet.
- Observed behavior
- Single-sheet counts or manual review cannot return the consolidated unique total across all 18 sheets.
Confirm that every sheet uses the same Company Name header and that the data starts in the same column or range. Inconsistent headers, extra rows, or blank cells affect the consolidated unique count.
Use a Dynamic Array Formula to Count Unique Company Names Across All Sheets
Best for WPS Office versions that support dynamic arrays because it returns the distinct count in one cell and updates when source data changes.
This method stacks the Company Name ranges from all 18 sheets into one virtual array, extracts unique names, and counts them with COUNTA.
Click the sheet tab where the result will appear, or click the plus icon beside the sheet tabs to insert a new summary sheet.
In a blank cell, type =COUNTA(UNIQUE(VSTACK(, then click the first data sheet, select its Company Name column range such as A2:A100, type a comma, repeat for each of the 18 sheets, and close all parentheses before pressing Enter.
The cell displays the number of unique company names. Extend the selected ranges if any Company Name column contains more rows than originally selected.
Consolidate Company Name Columns and Use Remove Duplicates
Use this method when you need a static unique list and count or when your WPS Office version does not support dynamic array functions.
Count Unique Company Names Across Multiple Sheets in WPS Office
Use WPS Office Spreadsheets to consolidate and count distinct company names with familiar Excel-style formulas or built-in data tools while keeping full compatibility with .xlsx workbooks.
- 1. Download WPS Office: Click Download WPS Office Free, install the suite, and open WPS Office Spreadsheets.
- 2. Open the workbook: Click File > Open, select the 18-sheet .xlsx workbook, and click Open.
- 3. Add a summary sheet: Click the plus icon next to the sheet tabs and name the new sheet Summary.
- 4. Count unique company names: Use the recommended VSTACK/UNIQUE formula or the Data > Remove Duplicates workflow to get the distinct company name count.
- 5. Save the result: Click File > Save As, choose Excel Workbook (*.xlsx), and click Save to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
Does the VSTACK and UNIQUE formula work in WPS Office?
It works in WPS Office versions that support dynamic array functions. If you see a #NAME? error, update WPS Office to the latest version or use Data > Remove Duplicates after consolidating the Company Name columns.
Can I count unique company names without typing all 18 sheet names in the formula?
Yes. Copy each Company Name column into one summary column, select the range, and click Data > Remove Duplicates. Then use COUNTA on the cleaned list. If your version supports Power Query, you can also append the sheets there.
How do I stop blank rows from being counted as unique values?
Select only the populated Company Name rows when copying or referencing ranges, or filter out blanks before removing duplicates. Blank cells in the consolidated range will otherwise appear as an extra unique item.
Will the Remove Duplicates method update automatically when a source sheet changes?
No. Remove Duplicates produces a static list. Use a dynamic-array formula or a Power Query connection and refresh it to get an updated count after the source data changes.
