Pivot Table Issues

How to Sum Selected PivotTable Values for Specific Regions

Rana Garcia Oct 22, 2026 869 views

Question details

The user needs to calculate the sum of specific items within a PivotTable (such as summing values for Region 1 and 2 in the East) without combining everything into the standard grand total.

How to Sum Selected PivotTable Values for Specific Regions

Product: Spreadsheet
Device & OS: not provided
Scenario: Performing data analysis where customized sub-totals or filtered grand totals are required based on multiple specific criteria.
Observed behavior: A standard PivotTable grand total automatically sums all items currently included in the table, lacking the ability to display a dedicated total for only a subset of items without applying strict filters or advanced DAX measures.

Before you start

Ensure your source data is organized in a tabular format with clear column headers (e.g., 'Region', 'Direction', 'Sales') and contains no blank rows or columns before creating your PivotTable.

Solution 1Recommended

Use PivotTable Filters or Slicers

The most straightforward way to calculate totals for specific criteria is by applying standard filters or visual Slicers to your PivotTable to exclude unwanted regions.

By filtering the PivotTable, the Grand Total will automatically update to sum only the visible items. This method is ideal for quick analysis where you only need to view the selected regions.

Insert a PivotTable

Select your data range, navigate to the 'Insert' tab, click 'PivotTable', and choose where to place it.

Configure PivotTable Fields

Drag the 'Region' and 'Direction' fields to the Rows or Filters area, and drag your numerical data field (e.g., Sales) to the Values area.

Apply Region Filters

Click the drop-down arrow next to the Region field label in the PivotTable. Check the box for 'Select Multiple Items', check only Region 1 and Region 2, and uncheck Region 3.

Use a Slicer for Easier Filtering (Optional)

Go to 'PivotTable Analyze' > 'Insert Slicer'. Select 'Region' and 'Direction'. You can now click the buttons on the Slicer to dynamically filter the Grand Total.

Dynamic Grand Totals: When you use Slicers, your Grand Total will instantly recalculate to reflect only the actively selected buttons.

Solution 2

Create a Power Pivot DAX Measure

For a reusable calculation that computes specific sums without hiding other data in the PivotTable, load your source data into the Data Model and create a custom DAX measure.

PivotTable Analysis

Effortlessly Manage PivotTables with WPS Office

Use WPS Spreadsheet to quickly filter, analyze, and sum specific PivotTable values. It fully supports standard PivotTable functions, interactive slicers, and offers seamless compatibility with Microsoft Excel files.

1. Open your Dataset: Launch WPS Spreadsheet and open your existing workbook containing the regional data.
2. Insert a PivotTable: Select your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Organize Your Data: Drag 'Region' to the Rows field and your numerical value to the Values field.
4. Filter the Grand Total: Click the filter icon on the Region row label, select only the specific regions you want to sum, and click OK. The Grand Total will update immediately.

100% compatibility with Microsoft Excel formats (.xlsx)Easy-to-use Slicers and Filters for advanced PivotTable data analysisLightweight and fast performance for processing large datasetsFree to use with powerful built-in analytical tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable grand total include hidden items?

By default, standard PivotTable filtering removes hidden items from the grand total. If your totals are not updating as expected, right-click the PivotTable, select 'PivotTable Options', navigate to the 'Totals & Filters' tab, and ensure your settings are configured to calculate based on visible items.

Can I sum specific values without filtering out the rest of the table?

Yes. You can achieve this by adding the data to the Data Model and writing a specific DAX measure using the CALCULATE function. This calculates totals based on specified criteria in the formula itself, regardless of the PivotTable's visual filters.

What is a Slicer and how does it help with PivotTables?

A Slicer is a visual filtering tool that provides clickable buttons to filter data in PivotTables quickly. It clearly indicates what data is currently included in your sums and grand totals, making it easier to present and adjust specific regional data.

Excel & Data Problems