How to Summarize Categories with Total Values in Excel
Question details
The user wants to create a dynamic summary table that extracts unique categories from a dataset and calculates the total values associated with each category.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and consolidating raw data to instantly view total aggregated values per unique category without manual calculation.
- Observed behavior
- A two-column dynamic array displaying each unique category alongside its calculated total.
Ensure your dataset does not contain empty rows within the target ranges, and verify that your version of Excel supports dynamic array functions like HSTACK and UNIQUE.
Use Dynamic Array Formulas (HSTACK, UNIQUE, SUMIFS)
Combine HSTACK, UNIQUE, and SUMIFS to generate a live, automatically updating summary table.
This method utilizes modern dynamic array functions to instantly pull unique categories and map their corresponding sums into a neat two-column format.
Locate the range containing your categories (e.g., C5:C30) and the range containing the values you want to total (e.g., D5:D30).
Click on an empty cell where you want the top-left corner of your summary table to appear.
Type the formula: =HSTACK(UNIQUE(C5:C30),SUMIFS(D5:D30,C5:C30,UNIQUE(C5:C30))) into the formula bar.
Press Enter. The formula will automatically spill down, creating a two-column summary of each unique category and its total.
Create a PivotTable for Category Summaries
A standard, widely compatible method to quickly summarize data by categories without writing complex formulas.
Easily Summarize Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE and SUMIFS, making data summarization quick and seamless. Enjoy a highly compatible, familiar interface to handle all your reporting needs.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your raw categorized data.
- 2. Apply dynamic formulas: Click on an empty cell and enter the formula: =HSTACK(UNIQUE(range1), SUMIFS(range2, range1, UNIQUE(range1))).
- 3. View your summary: Press Enter to instantly populate your dynamically categorized totals without manual recalculations.
Frequently Asked Questions
Why is the HSTACK or UNIQUE function returning a #NAME? error?
The #NAME? error occurs if your spreadsheet software version is older and does not support dynamic array functions. Try upgrading to the latest version of your software or use the PivotTable method to summarize your categories instead.
Can I sort the summarized categories by total value automatically?
Yes, you can wrap the SORT function around your formula. For example, using =SORT(HSTACK(UNIQUE(C5:C30), SUMIFS(D5:D30, C5:C30, UNIQUE(C5:C30))), 2, -1) will sort the final summarized results in descending order based on the total values in the second column.
Will the summary update automatically if I add new data to the bottom of my list?
If you use fixed cell references (like C5:C30), the formula won't include data added outside that specific range. To make your formula fully dynamic, convert your original dataset into a Table (Ctrl+T) and use structured table references (e.g., Table1[Category]) in your formula.
