Calculation Issues

How to Exclude Specific Cells from an Excel AutoSum Calculation

Natalie TaylorNatalie Taylor Oct 20, 2026 875 views

Question details

The user needs a method to exclude specific values from an Excel AutoSum total calculation dynamically, without needing to manually edit the SUM formula every time data changes.

How to Exclude Cells from an Excel AutoSum Calculation
Product
Excel
Device & OS
not provided
Scenario
Calculating column totals where certain rows or values need to be omitted dynamically from the final result.
Observed behavior
Standard AutoSum includes all cells within the selected range, requiring users to find a dynamic way to omit values by either clearing them or using criteria-based formulas.
Before you start

Identify whether you need to permanently remove the excluded data from your sheet or if you must keep the values visible but simply ignore them in the final total calculation.

Solution 1Recommended

Use the SUBTOTAL Function and Clear Contents

Ideal when you no longer need the data in the excluded cells. Clearing the cell automatically updates the SUBTOTAL result without formula edits.

The SUBTOTAL function is highly adaptable for basic lists and totals. By using the function number 9 (which represents SUM), the calculation will automatically recalculate if the values within the targeted range are deleted.

1
Select the destination cell

Click on the cell at the bottom of your data where you want the final total to appear.

2
Enter the SUBTOTAL formula

Type =SUBTOTAL(9, A:A) in the formula bar, replacing 'A:A' with the specific column or range of your data.

3
Clear contents to exclude

To exclude a cell from the calculation, simply select that specific cell and press the Delete key to clear its contents. The total will instantly update to reflect the exclusion.

Use the SUBTOTAL Function and Clear Contents
Dynamic Recalculation: Because the formula targets the entire column, any new data entered or deleted in that column will be processed automatically without rewriting the formula.
Smart Calculation Tool

Easily Exclude Cells from Calculations in WPS Spreadsheet

WPS Spreadsheet fully supports advanced calculation features including SUBTOTAL and SUMIFS. It provides a lightweight, highly compatible environment to handle complex data and dynamic calculations seamlessly.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the values you want to sum.
  2. 2. Access the Formulas tab: Navigate to the 'Formulas' tab on the top ribbon and click on 'Insert Function'.
  3. 3. Select your function: Search for and select either the SUMIFS or SUBTOTAL function depending on whether you are using criteria or clearing cell contents.
  4. 4. Apply the calculation: Follow the on-screen prompt to input your data range and criteria, then press Enter to instantly view your dynamic total.
Fully compatible with Microsoft Excel formulas, functions, and formats (XLSX).Intuitive AutoSum and function insert tools for faster data processing.Lightweight software that handles large datasets without lagging.
microsoft office alternative - wps office

Frequently Asked Questions

Can I hide rows to exclude them from AutoSum?

A standard SUM formula will still calculate hidden rows. However, if you use =SUBTOTAL(109, A:A) instead of function number 9, the spreadsheet will automatically exclude any rows you manually hide from the calculation.

How do I exclude text or blank cells in a sum?

You do not need to do anything special; standard SUM and SUBTOTAL functions automatically ignore text strings and completely blank cells in a given range.

Is it possible to exclude multiple non-adjacent cells using AutoSum?

AutoSum defaults to selecting a continuous range. To sum non-adjacent cells or manually exclude scattered ones without using formulas, hold the Ctrl key while clicking only the specific cells you want to sum (e.g., =SUM(A1:A3, A5, A7)).