Aggregate Excel Rows by Category, Product, or Date

How-to basics · Updated November 18, 2026

To aggregate Excel rows, select your source data and choose Insert > PivotTable; place the field you want to group by in Rows and the numeric field in Values, then set the Values calculation to Sum, Count, or another suitable summary. For a formula-based total by category, use SUMIF for one condition or SUMIFS for several.

What It Means to Aggregate Rows

Aggregating rows means combining detailed records into a smaller summary. For example, a sales sheet might contain one row per order, while an aggregate shows total sales by region, product, or month. The original records remain available; the summary makes patterns easier to read and compare.

The word “aggregate” can also refer to Excel’s AGGREGATE function. That function calculates a result such as a sum or average from a range and can, depending on its options, ignore errors or hidden rows. It does not automatically group records by category. If you want totals for each distinct category, use a PivotTable, SUMIF/SUMIFS, or another grouping method.

Before choosing a method, decide what the summary should answer. A total by region needs one grouping field; a total by region and product needs two. A count of orders is different from a sum of sales, even if both use the same source rows.

Prepare the Source Data

Good summaries depend on consistent source data. Arrange the records as a rectangular table: one header row, one record per row, and one type of information per column. For instance, use separate columns for Region, Product, Order Date, and Sales rather than combining a region and product in one cell.

Check these details before summarizing:

  • Give every column a unique, nonblank header.
  • Use consistent category names, such as “East” throughout rather than a mixture of “East” and “E.”
  • Store dates as Excel dates and amounts as numbers, not text that only looks numeric.
  • Remove fully blank rows and columns from the middle of the source range.
  • Keep each row at the same level of detail. If one row is an order and another is a monthly subtotal, combining them can double-count values.

Converting the range to an Excel Table helps the source expand when new records are added. On Windows, select a cell in the data and press Ctrl+T; on Mac, use Command+T. Confirm that the table has headers. A PivotTable built from a table can use the expanded table after you refresh it.

Use SUMIF for One Condition

SUMIF adds values from rows that meet one criterion. Imagine a small dataset with Region in A2:A6, Product in B2:B6, and Sales in C2:C6:

  • East, Widget, 120
  • West, Widget, 90
  • East, Gadget, 75
  • East, Widget, 30
  • West, Gadget, 110

With regions in A2:A6, sales in C2:C6, and the criterion East in E2, enter =SUMIF($A$2:$A$6,E2,$C$2:$C$6). It returns 225: the East sales are 120 + 75 + 30. The first range is checked for the criterion, E2 supplies the criterion, and the final range contains the values to add.

The dollar signs keep the source ranges fixed if you copy the formula down for other categories. Put West in E3 and copy the formula into the next row; it returns 200 (90 + 110). SUMIF is useful for a compact report alongside a dataset, but you must supply the category labels yourself.

Use SUMIFS for Multiple Conditions

SUMIFS adds values only when every stated condition is met. This is useful when the question is not simply “How much did each region sell?” but “How much did each region sell of a particular product?” Its first argument is the sum range, followed by pairs of criteria ranges and criteria.

With regions in A2:A6, products in B2:B6, sales in C2:C6, East in E2, and Widget in F2, enter =SUMIFS($C$2:$C$6,$A$2:$A$6,E2,$B$2:$B$6,F2). The result is 150, from the two East Widget records (120 + 30). Each criteria range must have the same dimensions as the sum range.

More criteria can be added as further range-and-criterion pairs, such as a date range for a period. Be clear about date boundaries: a condition for a single date is not the same as a condition for all dates in a month. For monthly reporting, use a start date and an exclusive end date, or add a month field to the source so the grouping rule is visible.

Build a PivotTable Summary

A PivotTable is often the quickest way to explore several groupings without writing formulas. In Excel for Microsoft 365 on Windows, click inside the source data and choose Insert > PivotTable. Confirm the table or range, choose where to put the PivotTable, and select OK. In the PivotTable Fields pane, drag a category such as Region to Rows and Sales to Values.

Excel usually summarizes numeric values with Sum. If it shows Count instead, check whether the Sales cells are stored as numbers; text entries can cause a count or an incomplete total. To change the calculation, open the menu for the value field, choose Value Field Settings, and select Sum, Count, Average, or another available calculation. You can also drag Product to Columns to compare products across regions, or add Order Date to Rows for a time-based view.

For example, grouping the sample data by Region and summing Sales produces East = 225 and West = 200. Dragging Product into the layout can further split those totals. The PivotTable does not normally alter the source records, so it is a good choice when you want a separate, interactive summary. When the source changes, right-click the PivotTable and choose Refresh. Excel for the web can work with PivotTables, though available editing features can differ from desktop Excel; Power Query authoring is also more limited on some platforms.

Create a Formula Summary That Expands

In Microsoft 365 or Excel 2021, UNIQUE can return a list of distinct categories that spills into cells below its formula. With region names in A2:A6, enter =UNIQUE(A2:A6) in E2. It returns East and West, in the order they first appear. If the source range is an Excel Table, structured references can make formulas easier to maintain as records are added.

To total each listed region, put the category label in E2, keep regions in A2:A6 and sales in C2:C6, and enter =SUMIF($A$2:$A$6,E2,$C$2:$C$6) in F2. It returns 225 for East; copy it beside the next category to return 200 for West. This approach combines an automatically generated category list with a familiar total formula. Keep the formula output area clear, because a spill formula needs empty cells to display its results.

What the AGGREGATE Function Does

AGGREGATE is for calculating a selected operation over a range, not for producing one total per group. It can perform operations such as SUM, AVERAGE, COUNT, and LARGE, and its options can exclude certain values. This makes it useful in some calculations where a range contains errors or hidden rows.

With sales values in C2:C6, enter =AGGREGATE(9,6,C2:C6) to calculate their sum while ignoring error values. The result is 425 (120 + 90 + 75 + 30 + 110). In this syntax, 9 selects SUM and option 6 tells Excel to ignore errors. This still returns one result for the range; it does not separate East from West. Use SUMIF or a PivotTable for that.

Choose the Right Method

The best approach depends on whether you need a quick exploration, a fixed report, or a formula that feeds other calculations:

  • PivotTable: Choose this for interactive summaries, multiple fields, and quick changes to row or column groupings.
  • SUMIF: Choose this for a direct total based on one condition, especially in a report with a known set of categories.
  • SUMIFS: Choose this when a total must satisfy two or more conditions.
  • UNIQUE with a summary formula: Choose this when you want a formula-generated list of categories and use a version that supports dynamic arrays.
  • AGGREGATE: Choose this for a calculation over a range when its available options for ignoring errors or hidden rows match the task—not as a substitute for grouping.

If you need repeatable data cleaning and grouping steps on a large or regularly updated dataset, Power Query may be suitable. In desktop Excel for Windows, select the data and choose Data > From Table/Range. In the Power Query Editor, use Home > Group By, select the grouping column, and choose an operation such as Sum or Count for the value column. Load the result back to Excel and refresh the query when the source changes. Power Query is a transformation workflow rather than a cell formula, and its available connectors and editing tools vary by Excel version and platform.

Common Problems and Misconceptions

A summary that looks wrong is often caused by source-data inconsistencies rather than a broken calculation. For SUMIF, inspect the criteria values for extra spaces or spelling differences. For PivotTables, check whether amounts are numbers and whether the PivotTable has been refreshed since the source changed. Also confirm the selected source range includes every record.

Watch for these specific mistakes:

  • Counting instead of summing: In a PivotTable, change Value Field Settings to Sum and verify the source values are numeric.
  • Double-counting: Do not aggregate raw transactions together with subtotal rows that already include those transactions.
  • Dates grouped unexpectedly: Confirm that date cells are real dates and inspect the grouping level shown in the PivotTable.
  • New rows missing: Use an Excel Table as the source or update the source range, then refresh the PivotTable or query.
  • Thinking AGGREGATE means “group”: AGGREGATE calculates a function over a reference; grouping records by labels requires a separate method.

Frequently Asked Questions

How do I group rows and add their values in Excel?
Select the source data, choose Insert > PivotTable, put the grouping field in Rows and the numeric field in Values, then set the value calculation to Sum. For one criterion in a formula, SUMIF is another option.

What is the difference between SUMIF and SUMIFS?
SUMIF tests one condition; SUMIFS can test multiple conditions and adds a value only when all its criteria are met. In SUMIFS, the sum range is the first argument.

Does Excel’s AGGREGATE function group rows by category?
No. AGGREGATE calculates an operation over a range and can use options to ignore certain values. Use a PivotTable, SUMIF, or SUMIFS to summarize records by category.

Why does my PivotTable count values instead of adding them?
Excel may be treating one or more entries as text, or the value field may be set to Count. Check the source cells and change the field’s Value Field Settings to Sum when the data is numeric.

How do I include new rows in an Excel summary?
Make the source an Excel Table before creating the summary, then add records to the table. Refresh the PivotTable or Power Query output; formula ranges may also need to include the new rows unless they use table references.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.