Excel Pivot Table Filters: Sum Values the Right Way

Charts & pivot tables · Updated November 19, 2026

Yes. In an Excel PivotTable, choose a filter to show the records or categories you want, and set the numeric field to summarize by Sum; the PivotTable recalculates its displayed totals for the filtered view. For a category filter, open the field’s drop-down and select items; to hide categories according to their summed amount, use Value Filters.

How filtering changes a PivotTable sum

A PivotTable groups source records into categories and calculates a summary for each group. Filtering controls which records or groups are included in the view, while the Values area determines what calculation Excel performs on the numeric field. These are separate settings: filtering a field does not, by itself, change a value field from Count to Sum.

For example, a sales PivotTable might put Region in Rows and Sales in Values. Filtering Region to show North makes the PivotTable display the North sales total and any related subtotals or grand total for that filtered view. It does not delete the other regions’ records from the source data. Clear the filter and those regions can appear again.

It helps to distinguish two common uses of the word “filter”:

  • Filter records or categories: Select regions, dates, products, or other field items to include in the report.
  • Filter by a calculated value: Keep or hide categories based on a condition such as sales greater than a threshold or the top 10 items by sales.

The first kind answers questions such as “What were sales in North?” The second answers “Which regions had sales above $10,000?” A value filter operates on the PivotTable’s grouped results; it is not the same as filtering individual rows in the source table.

Confirm the value field is set to Sum

Excel often chooses Sum automatically when a field contains numbers, but a numeric column with blanks, text, or inconsistent entries can lead Excel to use Count instead. Check the calculation before interpreting a total.

  1. Click a number in the PivotTable’s Values area.
  2. Right-click and choose Summarize Values By, then select Sum. Alternatively, open Value Field Settings and choose Sum on the Summarize Values By tab.
  3. Choose Number Format in Value Field Settings if you also need to display the result as currency, a percentage, or another appropriate format. Number formatting changes how the value looks, not the calculation.

If Sum is unavailable or the result still behaves like a count, inspect the source column. Values that look like numbers may be stored as text, or the field may contain text entries. Correct the source data, refresh the PivotTable, and check the summary setting again. A blank in a numeric field does not contribute an amount; inconsistent data can also affect how Excel chooses the initial summary.

Filter categories to show a selected sum

To keep selected field items, use the filter arrow beside a row or column field in the PivotTable. In a compact layout, the arrow may appear beside Row Labels or Column Labels; in other layouts, it appears beside the field name. Clear Select All, tick the items to keep, and select OK. The Values area then summarizes the data represented by the remaining items.

For a field placed in the Filters area, use its report-filter control above the PivotTable and select an item. This is useful when a reader needs to switch the whole report between years, regions, or departments without changing the row layout. A report filter can generally show one item or all items; it is not a substitute for selecting a particular set of row-field categories.

Filters on different fields work together: selecting a year and a region narrows the report to records that meet both selections. When a result seems unexpectedly small, check every report filter, row-field filter, and column-field filter. A filter left over from an earlier question can change the result even if the visible rows look familiar.

Use Value Filters for thresholds and top items

A Value Filter is useful when the categories to include depend on their summed results. Open the drop-down for the row or column field, point to Value Filters, and choose a condition such as Greater Than, Between, or Top 10. In the dialog, select the value field—such as Sum of Sales—and enter the condition or ranking settings.

For instance, to show products with summed sales above $5,000, apply a Greater Than value filter to the Product field and base it on Sum of Sales. Excel hides products that do not meet the condition. The filter is evaluated using the PivotTable’s current grouping and other active filters, so changing the year or region can change which products qualify.

Top 10 is configurable: the dialog can rank a chosen number of items by a selected value field, and the count need not remain ten. Confirm that the correct value field is selected if the PivotTable contains more than one measure. A Top 10 filter on Sum of Sales will not give the same list as one based on Count of Orders.

One important distinction: a value filter decides which grouped items remain visible; it does not edit or remove the source records. It also does not mean “show source rows whose individual sale is greater than the threshold.” For that row-level question, filter the source data or build the PivotTable around a field that identifies the records of interest.

A worked example: sum North-region sales

Suppose a source table has columns for Region, Product, and Sales. Its six records are North—Pencil—120; North—Notebook—80; North—Folder—150; South—Pencil—50; South—Notebook—200; and South—Folder—100. The PivotTable has Region in Rows and Sum of Sales in Values. Filtering Region to North should leave a total of 350, because 120 + 80 + 150 = 350.

If the source data is in an ordinary worksheet range with Region in A2:A7 and Sales in C2:C7, this formula independently checks the North total:

=SUMIFS(C2:C7,A2:A7,"North")

With those stated records, the result is 350. This formula is not a PivotTable filter; it is a separate worksheet calculation that can help verify the filtered summary. In a PivotTable, choose North from the Region field’s filter instead. If there are several fields or extra filters, make sure the comparison uses the same criteria on both sides.

To build the report, select a cell in the source data, choose Insert > PivotTable, confirm the table or range, and choose where to place the PivotTable. In the PivotTable Fields pane, drag Region to Rows and Sales to Values. Verify that the Values field says Sum of Sales, then use the Region drop-down to show North. The exact placement of controls can differ with the PivotTable layout, but the field-list roles are the same.

Understand subtotals and grand totals

Subtotals summarize groups, while a grand total summarizes the data represented in the PivotTable. When a category filter hides items, Excel updates the displayed report to reflect the remaining selection. This is why the grand total after filtering can be smaller than the grand total when all categories are shown. It is not necessarily an error or a change to the underlying source data.

Be cautious when comparing a filtered PivotTable with a manually selected set of visible worksheet cells. A PivotTable’s result depends on its fields, filters, and calculation settings, not on which source rows happen to be visible elsewhere on the sheet. Also check whether the value field uses an option such as Show Values As; percentages, running totals, and differences are presentations derived from values and should not be mistaken for a simple sum.

If you need to hide the grand total or adjust subtotals for presentation, use the PivotTable Design tab’s layout and totals controls. Those display choices do not repair an incorrect filter or aggregation. First confirm which records are included and that the value field is summarized by Sum.

Refresh the PivotTable when source data changes

A PivotTable summarizes its source; changing a filter does not update that source. Likewise, adding or editing source records may not appear in an existing PivotTable until it is refreshed. In Excel for Microsoft 365 on Windows, click inside the PivotTable and choose PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. To refresh all PivotTables in the workbook, use Data > Refresh All.

If new rows are repeatedly added, format the source range as an Excel Table before creating the PivotTable. Tables expand when data is added directly below them, making the source easier to maintain. If a PivotTable was created from a fixed cell range, new rows beyond that range may not be included; check PivotTable Analyze > Change Data Source and update the range if needed.

Excel for Mac has PivotTable creation, filtering, and refresh features as well, though ribbon placement and some dialog details can differ by release. Excel for the web can work with many PivotTables, but some creation and editing options are more complete in the desktop app. If a command is not visible, check the PivotTable-specific ribbon tab while a PivotTable cell is selected.

Common problems and practical fixes

  • The result says Count, not Sum: Open Value Field Settings and select Sum. Then check for text values or inconsistent source entries and refresh.
  • The number does not change after selecting a filter: Confirm that the filter was applied to the field used in the report, and that the chosen item is not already the only item represented. Check other active filters too.
  • New records are missing: Refresh the PivotTable and check its source range. A fixed range may not include appended rows.
  • A product disappears after a value filter: Check the threshold, the selected value field, and any other filters. The condition is applied to the grouped result in the current report context.
  • The total differs from a worksheet calculation: Compare the source rows and criteria, confirm the PivotTable’s aggregation, and check for blank, text, or duplicate records. Ensure both calculations cover the same date range and categories.

For a clear audit trail, retain a copy of the source data, name fields descriptively, and label the PivotTable’s measure clearly—for example, “Sum of Sales” rather than an ambiguous “Total.” These habits make it easier to spot whether a mismatch comes from a filter, the source, or the chosen calculation.

When a PivotTable is not the right tool

PivotTables are useful for exploring categories, changing groupings, and comparing subtotals without writing a separate formula for every category. If the report needs one fixed total based on explicit criteria, a worksheet formula such as SUMIFS may be simpler. With Region in A2:A7, Sales in C2:C7, and Product in B2:B7, this formula totals North-region Notebook sales:

=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Notebook")

For the six-record example above, it returns 80. SUMIFS updates when its referenced cells change, but it does not provide the interactive field layout and category browsing of a PivotTable. A standard worksheet filter is another option when the task is to inspect individual source rows rather than summarize them. Choose based on whether you need an interactive summary, a fixed calculation, or filtered detail records.

Frequently Asked Questions

Does filtering a PivotTable change the source data?
No. It changes which records or grouped items appear in the report. The source data remains intact unless you separately edit or delete it.

Why does my PivotTable count values instead of summing them?
Excel may have detected text or inconsistent entries in the source field. Open Value Field Settings and choose Sum, then inspect the source column and refresh the PivotTable if necessary.

Can I filter a PivotTable to show only values over a certain amount?
Yes. Open the relevant row or column field’s drop-down, choose Value Filters, select a condition such as Greater Than, and base it on the appropriate summed value field.

Does a PivotTable grand total include filtered-out categories?
For a standard PivotTable view, the displayed total reflects the data represented by the active filters. Clear the filters to return to the broader total. Special calculations such as Show Values As can change how displayed figures are derived.

Why are newly added rows missing from the sum?
Refresh the PivotTable and confirm that its source includes the new rows. If the source is a fixed range, update it or use an Excel Table as the source for a report that will grow.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.