How to sum values by group in Excel?
In daily data analysis and reporting tasks, you may often encounter tables where a particular category or product appears multiple times, and you need to summarize or aggregate related numerical values by each group. For example, in the table below, you have two columns: one lists product names and the other shows sales figures. The practical challenge is to quickly sum total sales for each product, so that you can clearly see the overall sales performance of every individual product, as illustrated in the screenshot.
Sum values by group with formula
Calculate or combine values by group with Kutools for Excel
Sum values by group with Pivot Table
Sum values by group with formula
When you want to summarize values by group within an Excel table without using any add-ins or advanced features, you can achieve it directly with a formula. This approach is suitable for small to medium-sized datasets and for users who prefer simple, repeatable solutions.
Note: This method works best if your data is already sorted by the grouping column (product name in this example).
Select the cell immediately to the right of your existing data (for example, if your data ends at column B, select cell C2), and enter the following formula:
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))Parameter explanations:
- A2 - the cell in the current row containing the group name (e.g., product)
- A1 - the cell above, to compare whether the current row is a new group
- A:A - the column with group names
- B:B - the column with the values to sum
After typing the formula, press the Enter key. Then use the fill handle (the small square at the bottom-right corner of the cell) to drag the formula down alongside your data. The formula will return the sum for each group on the first appearance of that group and leave other group rows blank.
Applicable scenarios: This approach is simple and transparent, ideal for standard Excel users who want to avoid extra features or add-ins. It’s best when your data is sorted and not exceptionally large.
Limitations: If your groups are not sorted together, or if you later insert new data, the formula may need adjustment or reapplication. For more complex grouping, consider using a Pivot Table or Kutools.
Troubleshooting tip: If the formula returns zero or incorrect results, check that there are no extra spaces or invisible characters in the grouping column.
Calculate or combine values by group with Kutools for Excel
If you want to perform more advanced calculations on your grouped data, such as counting entries, finding max or min values, or even combining values in a group into a single cell, Kutools for Excel offers the Advanced Combine Rows utility for a fast, flexible solution without manual formula work.
After free installing Kutools for Excel, please do as below:
1. Select the entire data range (including your group and value columns). Then go to Kutools > Merge & Split > Advanced Combine Rows.
2. In the Advanced Combine Rows dialog:
- Specify one column as the key column upon which you want to group the data (for example, "Product").
- In the value column (e.g., "Sales"), select the operation you need—such as Sum, Count, Max, Min, or Combine.
- You can set operations for multiple columns at once if desired.
3. Click OK, and your table will instantly calculate or combine values by group. See the sample results below for summing or combining grouped data:
| Sum by group | Combine by group |
 |  |
Advantages: This method is fast, convenient, and supports various summary and combination operations. It does not require formula knowledge, making it suitable for users seeking efficiency or processing large tables.
Tips: Use the backup function of Kutools or save your file before performing bulk operations for additional security.
Sum values by group with Pivot Table
Another practical and highly interactive approach to summing values by group in Excel is to use a Pivot Table. Pivot Tables allow you to group, sum, and analyze your data efficiently—especially ideal for large datasets, dynamic layouts, or when you need to quickly adjust grouping and aggregation without manual editing.
Applicable scenarios: Pivot Tables are recommended when working with large or frequently changing datasets, or when you need to quickly generate summary reports with the flexibility to regroup or filter by different columns.
- Select any cell within your data range.
- Go to the Insert tab, then click PivotTable.
- In the dialog box, confirm the table range and choose where to place the Pivot Table (a new worksheet is the default; you may select an existing worksheet if preferred).
- In the Pivot Table Field List, drag the grouping column (for example, Product) into the Rows area.
- Drag the values column (such as Sales) into the Values area. By default, it will sum the sales amounts for each group.
You will see a summary table that automatically calculates the total value for each group. You can further expand, collapse, or filter groups, and you can easily update or refresh the Pivot Table when data changes.
Advantages: Pivot Tables provide fast, flexible, and interactive data summaries with built-in grouping and aggregation. They are recalculated automatically when underlying data is updated.
Limitations: For very simple tasks or one-time operations, building a Pivot Table may feel like extra steps compared to a formula or Kutools. Additionally, formatting in Pivot Table areas can sometimes need adjusting, especially if the grouped results include many subcategories.
Tips and troubleshooting: If group names do not aggregate as expected, check for trailing or leading spaces in the source data. Refresh the Pivot Table when you update original data to ensure summaries stay accurate. If you need to perform non-standard calculations, adjust the field settings in the Pivot Table Value Field Settings dialog.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in