Function Problems

How to Combine and Count Unique Values from Multiple Excel Columns

WPS Content Manager Sep 28, 2026 870 views

Question details

The user needs to extract unique condition values from four separate columns, count the occurrences of each condition, and ensure the final count updates dynamically as source data changes.

How to Combine and Count Unique Values from Multiple Excel Columns

Product: Excel
Device & OS: not provided
Scenario: Data analysis and summarization where participant responses or conditions are spread across multiple columns and need to be flattened into a single, consolidated frequency report.
Observed behavior: A need for a method to combine multiple columns, return a single list of unique values, and display the corresponding counts side-by-side automatically.

Before you start

Check your Excel version. If you are using Microsoft 365 or Excel 2021, you can use the dynamic array formula method. For older versions (Excel 2019 and below), use the Power Query method instead.

Solution 1Recommended

Use a Dynamic Array Formula (LET, TOCOL, UNIQUE, COUNTIF)

Ideal for Microsoft 365 users, this single formula dynamically extracts unique values from multiple columns, counts them, and spills the results into your worksheet.

This method utilizes modern dynamic array functions. The LET function allows us to define variables within the formula for cleaner syntax. TOCOL flattens the multi-column data into a single column, UNIQUE extracts the distinct values, and COUNTIF calculates their frequencies.

Select a Destination Cell

Click on the empty cell where you want the top-left corner of your results table (unique values and counts) to appear.

Enter the Formula

Type the formula: =LET(r, 'Participant Info'!B2:E10, v, SORT(UNIQUE(TOCOL(r, 1))), w, COUNTIF(r, v), HSTACK(v, w)). Make sure to adjust the range 'Participant Info'!B2:E10 to match your actual data range.

Apply and View Results

Press Enter. The formula will automatically 'spill' the unique conditions into the first column and their corresponding counts into the adjacent column. As you update the source data in B2:E10, these results will refresh instantly.

Use a Dynamic Array Formula (LET, TOCOL, UNIQUE, COUNTIF)

Ignoring Blank Cells: The '1' in the TOCOL(r, 1) function tells Excel to automatically ignore blank cells within your selected range, preventing empty rows from appearing in your unique count.

Solution 2

Use Power Query and a PivotTable

Best for older Excel versions or larger datasets where you want a robust, repeatable data transformation pipeline without complex formulas.

Advanced Data Analysis

Easily Combine and Count Data in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas and PivotTables, making it incredibly simple to combine columns, extract unique values, and count data dynamically. It is lightweight, fast, and highly compatible with Microsoft Excel files.

1. Open Your Workbook: Launch WPS Spreadsheet and open the .xlsx file containing your multi-column data.
2. Apply Array Formulas: Select an empty cell and input standard data array formulas like UNIQUE and COUNTIF to process your ranges.
3. Create a PivotTable: Alternatively, highlight your data, go to the Insert tab, and select PivotTable to visually summarize your conditions.
4. Refresh Data: Watch your formula arrays update instantly upon data entry, or use the Refresh button to instantly update your PivotTable.

Fully compatible with Microsoft Excel (.xlsx) formats.Supports advanced array functions for dynamic reporting.Built-in PivotTable features for quick, multi-column data summarization.Free and lightweight alternative to heavy data processing tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my TOCOL or LET formula return a #NAME? error?

This error occurs if your version of Excel does not support dynamic array formulas (e.g., Excel 2019 or older). You will need to upgrade to a newer version like Microsoft 365, or use the Power Query method instead.

Will the Power Query PivotTable update automatically if I add new data?

Not instantly. While the source table updates dynamically, you must right-click the PivotTable and select 'Refresh' (or go to Data > Refresh All) to see the latest unique values and counts.

How can I ignore blank cells when combining columns with a formula?

In the dynamic array formula, the TOCOL function includes a second argument for ignoring blanks. Using TOCOL(range, 1) tells the software to ignore empty cells in the array automatically.

Can I count unique values across non-adjacent columns?

Yes. You can use the HSTACK or VSTACK function within your LET formula to combine non-adjacent ranges before passing them to the UNIQUE and COUNTIF functions.

Excel & Data Problems