Combine Numbers and Their Counts in One Excel Cell
Question details
The user wants to convert a horizontal list of numbers into a single text string that displays each distinct number along with its count, omitting the count if the number appears only once.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Consolidating and reporting numerical data where a horizontal range needs to be summarized into a single cell showing unique values and their frequency.
- Observed behavior
- The goal is to output a text string formatted as 'Number:Count' separated by commas, automatically dropping the ':Count' suffix when the frequency is exactly 1.
Ensure you are using a modern spreadsheet version (such as Microsoft 365 or the latest WPS Office) that supports dynamic array functions like LET, UNIQUE, and HSTACK.
Combine Numbers and Counts Using the LET Function
Use a single, advanced dynamic array formula to extract unique values, count their occurrences, and join them into a formatted text string.
This method leverages dynamic array functions to process the data in memory. The LET function allows you to define variables for the unique numbers and their conditional counts, keeping the formula clean and efficient.
Click on the empty cell where you want the combined result to be displayed.
Type or paste the following formula: =LET(numbers,TRANSPOSE(UNIQUE(A1:H1,TRUE)),counts,IF(COUNTIF(A1:H1,numbers)=1,"",":"&COUNTIF(A1:H1,numbers)),array,HSTACK(numbers,counts&","),result,TEXTJOIN("",FALSE,array),LEFT(result,LEN(result)-1))
Press Enter. If your data is in cells A1:H1 (e.g., 1, 2, 3, 4, 5, 2, 2, 5), the cell will display 1,2:3,3,4,5:2.
Summarize and Combine Data Easily with WPS Office
WPS Spreadsheet provides robust support for advanced formulas, dynamic arrays, and complex data manipulation, making it simple to consolidate numbers and counts into single cells.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the numbers.
- 2. Paste the formula: Click on your target cell and paste the provided LET formula into the formula bar.
- 3. Adjust data range: Change the range 'A1:H1' in the formula to match the actual row containing your data.
- 4. View results: Press Enter to instantly view the consolidated unique numbers and their counts.
Frequently Asked Questions
Why does my formula return a #NAME? error?
This error usually occurs if your spreadsheet software is an older version that does not support newer dynamic array functions like LET, UNIQUE, or HSTACK. Try updating your software or use the latest version of WPS Office.
Can I use this formula for vertical columns instead of horizontal rows?
Yes. To apply this to a vertical column (e.g., A1:A10), remove the TRANSPOSE function and the 'TRUE' argument from the UNIQUE function. Use UNIQUE(A1:A10) and adjust the COUNTIF ranges accordingly.
How can I change the separator from a colon to something else?
In the formula segment ":"&COUNTIF(...), simply replace the colon within the quotation marks with your preferred separator, such as a hyphen "-" or parentheses "(".
What happens if there are empty cells in my data range?
Empty cells might be counted as zeros. To avoid this, you can filter out blanks by nesting the FILTER function inside your UNIQUE formula, like UNIQUE(FILTER(A1:H1, A1:H1<>""), TRUE).
