How to Calculate Filtered Employee Sales Totals in Excel
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.

- 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.
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.
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.
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.
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.
Click the cell in the Total Row directly under your sales column, click the dropdown arrow that appears, and select Sum.
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.
Use SUMIFS to Calculate Totals by Employee
Use the SUMIFS function if you want to display the total sales for a specific employee in a separate cell without having to filter the main table.
Summarize Employee Sales with a PivotTable
PivotTables are ideal for viewing total sales for all employees simultaneously without manually filtering a list.
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. Open data in WPS: Launch WPS Spreadsheet and open your sales data file.
- 2. Insert Table: Select your dataset, go to the Insert tab, and click Table to format your data.
- 3. Enable Total Row: Navigate to the Table Tools tab that appears and check the Total Row option.
- 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.

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.




