Calculate Totals for Unique Labels in Excel with a PivotTable
Question details
Create a summary that lists each unique label once and calculates the total of all values associated with that label.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The source data contains duplicate labels, such as brands, and a numeric column such as clicks that must be totaled by label.
- Observed behavior
- The desired result is one row per unique label with the sum of its related numeric values.
Make sure your source table has a header row and that the value column contains real numbers rather than text. Remove blank rows within the data range before creating a PivotTable.
Create a PivotTable to total values by each unique label
Use this method when you need a flexible summary that can be sorted, filtered, and refreshed as the source data changes.
A PivotTable groups duplicate labels automatically. Place the label field in Rows and set the numeric field in Values to Sum to show one total for every label.
Click any cell in the data range that contains the label column, such as Brand, and the numeric column, such as Clicks.
In Excel, open Insert > PivotTable, confirm the table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, drag Brand or your label field to the Rows area.
Drag Clicks or your numeric field to the Values area. Excel should display Sum of Clicks.
If the field displays Count instead of Sum, select the value field, choose Value Field Settings, select Sum, and choose OK.
Use a dynamic array formula to list unique labels and totals
Use this option in Excel 365 when you want a formula-based summary that updates automatically without a PivotTable.
Summarize Unique Labels and Totals with a WPS Spreadsheet PivotTable
WPS Spreadsheets lets you group duplicate labels and calculate their totals in a PivotTable. Create a pivot from your data, place the label in Rows, and add the numeric field as a Sum value to build an editable summary quickly.
- 1. Select the data range: In WPS Spreadsheets, click a cell inside the range containing your label and value columns.
- 2. Create a PivotTable: Go to Insert > PivotTable and confirm the source range and destination.
- 3. Add the label as a row field: In the PivotTable field list, drag the label field, such as Brand, to the Rows area.
- 4. Add the values as a sum: Drag the numeric field, such as Clicks, to the Values area, then select Value Field Settings and choose Sum if needed.
Frequently Asked Questions
Why does my PivotTable show Count instead of Sum?
Excel uses Count when it detects nonnumeric entries in the source field. In the PivotTable, select the value field, choose Value Field Settings, and select Sum. Also check that the source values are stored as numbers.
How do I refresh totals after adding more source data?
Click inside the PivotTable and choose PivotTable Analyze > Refresh. For expanding datasets, convert the source range to a table first through Insert > Table so new rows are included more easily.
Can I sort the unique labels by their total values?
Yes. Right-click a number in the PivotTable total column, select Sort, and choose Largest to Smallest or Smallest to Largest.
What Excel version is required for the UNIQUE and HSTACK formula?
The formula requires a version of Excel that supports dynamic arrays, including UNIQUE and HSTACK, such as Microsoft 365. If those functions are unavailable, use a PivotTable or create a unique-label list and apply SUMIF.
