Addition in Excel PivotTables: Sum Values Clearly

How-to basics · Updated November 18, 2026

To add values in an Excel PivotTable, drag the numeric field into the Values area; Excel usually summarizes it with Sum. If it displays Count instead, right-click a value in the PivotTable and choose Summarize Values By > Sum.

What addition means in a PivotTable

A PivotTable adds records from your source data and presents the totals by categories you choose. For example, a sales list might have one row per transaction, with columns for date, region, product, and amount. Putting Region in Rows and Amount in Values creates a total for each region, plus a grand total if that option is enabled.

This is different from typing an addition formula into a cell. A PivotTable groups matching records and aggregates their values, so you can change the grouping without rewriting formulas. You could show totals by region, then move Product to Rows to see product totals, or put Region in Columns to compare regions side by side.

In a PivotTable, the Values area is the key: a field placed there is summarized. Numeric fields commonly default to Sum, while text fields commonly default to Count. Excel may also choose Count for a column that appears numeric but contains text or other nonnumeric entries.

Create a PivotTable and add a total

In Excel for Microsoft 365 on Windows, start with a clean source table: one header row, one record per row, and a separate column for each kind of information. Click inside the data, choose Insert > PivotTable, check the proposed table or range, select where to place the PivotTable, and click OK. A new worksheet is a convenient choice when you want the summary separate from the source.

  1. In the PivotTable Fields pane, drag a category such as Region or Month to Rows.
  2. Drag the numeric field you want to add, such as Sales, to Values.
  3. Check that the Values label says Sum of Sales. If it says Count of Sales, change the summary to Sum.
  4. To show a second breakdown, drag another category to Columns or place it below the first field in Rows.

For instance, if a source has three sales for North—120, 80, and 50—the PivotTable shows 250 for North when Sales is summarized by Sum. Adding Product to the layout can split that 250 into product totals, while the grand total still represents the underlying records included in the PivotTable.

You can add another measure by dragging a different numeric field into Values. You can also put the same field in Values more than once, then set each copy to a different summary, such as Sum and Average. This is useful when a report needs both total sales and average transaction size.

Choose Sum instead of Count

If the PivotTable counts rows rather than adding amounts, right-click any number in that value column and choose Summarize Values By > Sum. You can also open Value Field Settings from the field’s menu in the PivotTable Fields pane, select Sum under Summarize Values By, and choose OK. Menu wording or placement can vary slightly with the Excel interface, but Value Field Settings is the control for changing how that field is summarized.

Count answers how many nonblank entries are present; Sum adds numeric values. For example, if a category has amounts 10, 15, and 20, Sum returns 45 and Count returns 3. Neither result is inherently wrong: choose the one that answers the question your report is meant to answer.

After choosing Sum, format the result if needed. Right-click a value, choose Number Format, and select a suitable format such as Currency or Number. Using Number Format through the value field settings applies formatting consistently to the PivotTable field, whereas manually formatting a few visible cells may not carry through as the PivotTable changes.

How PivotTable totals are calculated

A PivotTable calculates a summary from the records currently included in its source and any active filters. If you filter the report to one year, its totals reflect that year rather than all years. Row and column labels determine how the records are grouped; they do not alter the original source data.

Grand totals add the groups represented in the report, subject to its filters and layout. Subtotals can show intermediate totals for nested row fields. You can control these display choices from the Design tab that appears when the PivotTable is selected, using the Subtotals and Grand Totals menus. Turning a grand total off hides it from the report; it does not change the underlying records.

Blank cells and zeroes are also different. A blank source cell does not contribute a number to a sum, while a numeric zero contributes zero. If the field is summarized by Count, the count generally reflects nonblank entries, so a cell containing zero can count as an entry. Consider whether missing amounts mean unknown, not applicable, or genuinely zero before interpreting a report.

Add arithmetic beyond a simple sum

Sometimes a reader means “add in a PivotTable” as in adding a calculated result, not totaling a source column. For a straightforward total, the Values summary is usually the right tool. For a calculation such as quantity multiplied by unit price, it is often simplest to add a calculated column to the source data and then refresh the PivotTable. Each source row can then contribute its calculated amount to a Sum.

For example, if a source has Quantity in column B and Unit Price in column C, add a Revenue column to the source table. With quantities in B2:B6 and prices in C2:C6, enter =B2*C2 in the first Revenue row and fill it down. If the five row revenues are 20, 30, 15, 40, and 25, the PivotTable can sum Revenue to 130. This arithmetic happens in the source table; the PivotTable groups and summarizes the resulting values.

A classic PivotTable can also offer a calculated field for calculations based on fields in the report. Select the PivotTable and look for PivotTable Analyze > Fields, Items, & Sets > Calculated Field. Availability depends on the PivotTable’s source and features: this command is not the general way to create measures for a PivotTable built with the Data Model. For Data Model reports, measures created with DAX are the more suitable approach for reusable calculations, but they require familiarity with the Data Model and measure concepts.

Common addition problems and fixes

When Sum is unavailable or produces an unexpected result, inspect the source column rather than assuming the PivotTable itself is broken. A column can contain a mixture of numbers and text—for example, entries like 25 and text-formatted entries like 25 units. Text that looks like a number may not behave as a number in a summary. Clean or convert the source values, remove unwanted text, and refresh the PivotTable.

  • It says Count: Change the summary to Sum, then check whether the source column contains text, error values, or inconsistent entries.
  • The new records are missing: Refresh the PivotTable and confirm that the source range includes the added rows. A regular range may not expand to cover new rows automatically.
  • The total looks too high: Check for duplicate source records, filters, and repeated transactions. A PivotTable adds every included record; it does not know that two rows might be duplicates.
  • The total looks too low: Check filters, hidden or excluded records, blanks, and values stored as text. Also confirm that the PivotTable’s source includes the complete intended data range.
  • New source values do not appear after editing: Select the PivotTable and choose PivotTable Analyze > Refresh, or right-click it and choose Refresh.

For data that will grow, turning the source range into an Excel table before creating the PivotTable can make source management easier. Select a cell in the data and choose Insert > Table, confirming that the table has headers. When rows are added directly below an Excel table, the table can expand to include them; refresh the PivotTable afterward so it recalculates from the updated source.

PivotTable addition compared with a SUM formula

A SUM formula is a better fit when you need a fixed total in a worksheet cell and do not need interactive grouping. With amounts in B2:B6, the formula =SUM(B2:B6) returns 100 if the values are 10, 15, 20, 25, and 30. The formula range must match the records you intend to include; if new entries fall outside B2:B6, the formula will not include them unless the range is adjusted or uses a suitable expanding source.

A PivotTable is more useful when you need to compare totals across categories, filter the report, or rearrange the view. It also makes subtotals and category breakdowns easier to inspect. The trade-off is that a PivotTable may need refreshing after its source changes, and its layout can be more than you need for a single total. Both methods can be correct; choose based on whether the task is a simple total or a changeable summary.

In Excel for Mac and Excel for the web, PivotTable features and the placement of commands can differ from the Windows desktop interface. The main approach remains the same: create or select the PivotTable, put the numeric field in Values, and set its summary to Sum. If a named command is not in the same location, use the field menu or the PivotTable-specific ribbon tab available in that version.

Frequently Asked Questions

How do I add a total row to an Excel PivotTable?
Click the PivotTable, open the Design tab, choose Grand Totals, and select an option that shows grand totals for rows, columns, or both. The grand total reflects the records and filters in the report.

Why does my PivotTable show Count of Sales instead of Sum of Sales?
Excel may have detected text or other nonnumeric entries in the source field, or the field may have been set to Count. Choose Summarize Values By > Sum, then check and clean the source column if the result is missing amounts.

Will a PivotTable update when I change the source data?
Not necessarily immediately. Select the PivotTable and choose PivotTable Analyze > Refresh. If you added rows outside a fixed source range, update the source range or use an Excel table as the source.

Can I sum the same field in two different ways?
Yes. Add the field to Values twice, then set each instance separately—for example, one to Sum and the other to Average. Give the fields clear custom names so readers can distinguish the results.

Can a PivotTable add across columns rather than down rows?
Yes. Put the category fields in Columns instead of Rows. The PivotTable still summarizes the Values field; moving a category changes how the result is arranged, not the underlying addition.

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.