Calculation Issues

How to Calculate Filtered Employee Sales Totals in Excel

Ayan MasoodAyan Masood Nov 10, 2026 868 views

Question details

The user needs to calculate the total sales for specific employees and have the sum automatically update when the data table is filtered.

How to Calculate Filtered Employee Sales Totals in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Filtering a large sales data table to view and sum up sales records for individual employees without including hidden data.
Observed behavior
Standard SUM formulas include hidden or filtered-out rows, requiring a specific method to only calculate the visible filtered rows.
Before you start

Ensure your sales data is formatted as an official Excel Table and contains no entirely blank rows within the dataset to ensure filters and totals apply correctly.

Solution 1Recommended

Add a Total Row to an Excel Table

The easiest way to sum only visible, filtered rows is by enabling the built-in Total Row feature in an Excel Table.

When you use the Table Total Row, Excel automatically inserts a SUBTOTAL function instead of a standard SUM. This ensures that any rows hidden by your filters are excluded from the final calculation.

1
Format as Table

Select any cell inside your sales dataset and press Ctrl + T to format it as a Table, making sure 'My table has headers' is checked.

2
Enable Total Row

Click anywhere inside the table, go to the Table Design tab on the ribbon, and check the Total Row box in the Table Style Options group. A new row will appear at the bottom.

3
Select the Sum function

Click the cell in the Total Row directly under your sales column, click the dropdown arrow that appears, and select Sum.

4
Filter by employee

Click the filter arrow on your Employee column header, select a specific employee's name, and click OK. The total at the bottom will automatically update to reflect only that employee's sales.

Understanding SUBTOTAL: The SUBTOTAL function used by the Total Row is specifically designed to sum values while ignoring any rows hidden by filters, providing dynamic calculations based on your current view.
Calculate Data Effortlessly in WPS

Use WPS Spreadsheet to Calculate Filtered Sales Totals

WPS Spreadsheet offers robust calculation features identical to Excel, including Table Total Rows, SUBTOTAL, and SUMIFS, allowing you to manage and analyze filtered employee sales data seamlessly and for free.

  1. 1. Open data in WPS: Launch WPS Spreadsheet and open your sales data file.
  2. 2. Insert Table: Select your dataset, go to the Insert tab, and click Table to format your data.
  3. 3. Enable Total Row: Navigate to the Table Tools tab that appears and check the Total Row option.
  4. 4. Calculate filtered sum: Click the dropdown in the new total row under your sales column and choose Sum to automatically calculate filtered results as you adjust the employee filter.
Fully compatible with Microsoft Excel (.xlsx) formulas, functions, and table formats.One-click Total Row feature for instant filtered data calculations.Advanced PivotTables and conditional summing (SUMIFS) available for free.Lightweight software with a tabbed interface for easier data management.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard SUM function include hidden rows?

The standard SUM function is designed to calculate the total of a specified range regardless of row visibility. To exclude rows hidden by filters, you must use the SUBTOTAL or AGGREGATE function, which are built to process only visible cells.

Can I sum multiple filtered columns using the Total Row?

Yes. Once the Total Row is added to your table, you can click on any cell in that bottom row, open the dropdown menu, and select Sum (or other metrics like Average or Count) for as many columns as you need.

What function number should I use in SUBTOTAL to ignore filtered rows?

When writing the formula manually, use function number 9 or 109 for Sum. Using 109 is generally recommended as it ignores both manually hidden rows and rows hidden by filters, whereas 9 only ignores filtered rows.

How do I remove the Total Row from my table?

Click anywhere inside your table, navigate to the Table Design tab (or Table Tools in WPS Spreadsheet), and uncheck the Total Row box. Your original data and filters will remain unaffected.