How to Count Unique Values Across Multiple Columns in Excel
Question details
The user wants to generate a list of unique items spanning three separate columns in a table and calculate the occurrence count for each unique item.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Consolidating data scattered across multiple columns into a single frequency summary table.
- Observed behavior
- The user needs a formula-based method to combine the columns, extract unique values, and count their frequencies.
Ensure your spreadsheet software supports Dynamic Array formulas like UNIQUE and TOCOL (available in Microsoft 365). If you are using an older version, you may need to rely on alternative methods like Power Query.
Use Dynamic Array Formulas (Microsoft 365)
The most efficient way to extract and count unique values across multiple columns is by combining the TOCOL, UNIQUE, and COUNTIF functions.
This method utilizes modern dynamic arrays to stack your columns into one vertical list, filter out duplicate values, and then tally the results automatically.
Select the cell where you want the unique list to start (e.g., E2) and enter the formula: =SORT(UNIQUE(TOCOL(DataTable[[Col1]:[Col3]],1)))
Press Enter. This formula converts the three columns into a single vertical list, removes blanks, extracts unique items, and sorts them alphabetically.
In the adjacent cell (e.g., F2), enter the formula: =COUNTIF(DataTable[[Col1]:[Col3]],E2#)
Press Enter. The '#' symbol ensures the COUNTIF function spills automatically to match the height of your dynamic unique list.
Consolidate and Count Using Power Query
If you don't have access to dynamic array formulas, Power Query is a powerful alternative for unpivoting and grouping data.
Analyze and Count Data Effortlessly with WPS Spreadsheet
WPS Office provides a comprehensive suite of data analysis tools and robust formula support, allowing you to easily manage, extract, and count unique values across complex datasets without needing expensive subscriptions.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your multi-column data.
- 2. Extract unique records: Use built-in functions or the advanced filtering tool from the 'Data' tab to consolidate unique records.
- 3. Apply counting formulas: Use functions like COUNTIF to quickly tally the occurrences of each unique item across your selected ranges.
- 4. Visualize your results: Instantly format your summary table or insert a chart to visualize the frequency of each unique item.
Frequently Asked Questions
Why does the TOCOL function return a #NAME? error?
The #NAME? error occurs if your version of the spreadsheet software does not support the TOCOL function. It is relatively new and available in Microsoft 365. For older versions, consider using Power Query.
Can I count unique items while ignoring blank cells?
Yes, the '1' parameter in the TOCOL function (=TOCOL(array, 1)) specifically instructs the software to ignore blank cells when combining the columns.
Does this method work if my data contains mixed text and numbers?
Yes, the UNIQUE and COUNTIF functions can handle alphanumeric data, text, and numbers seamlessly across the specified columns.
How do I handle case sensitivity in my unique list?
The standard UNIQUE function is not case-sensitive (e.g., 'Apple' and 'apple' are treated as the same). If you need exact case matching, you will need a more complex array formula using the EXACT function.
