Function Problems

How to Include or Exclude Excel Cells from a SUM Formula

Algirdas Jasaitis Sep 27, 2026 868 views

Question details

The user wants to selectively include or exclude specific cell values in a calculation without manually hiding rows or altering the main dataset structure.

How to Include or Exclude Cells from a SUM Formula in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating a total where certain values must be skipped dynamically based on user choice or a specific condition, like a date entry being present.
Observed behavior: The standard SUM function adds all numbers in a specified range unconditionally, requiring alternative functions to apply targeted inclusion criteria.

Before you start

Determine whether you prefer manually toggling values on and off using a 1/0 helper column, or if you want to automatically trigger inclusion based on existing data, such as a date entry.

Solution 1Recommended

Use SUMPRODUCT with a 1 or 0 Helper Column

Create a helper column where 1 includes the adjacent value and 0 excludes it, then calculate the total using the SUMPRODUCT function.

This method is highly flexible because it allows you to manually flag exactly which cells should be part of the final sum without affecting hidden rows or changing your actual data values.

Insert a helper column

Create a new column directly next to the values you want to sum. For instance, if your values are in range A1:A6, use B1:B6 as the helper column.

Enter inclusion flags

Type '1' in the helper cells next to the values you want to include in the total, and '0' next to the values you want to exclude.

Apply the SUMPRODUCT formula

Select an empty cell for your total and enter the formula: =SUMPRODUCT(A1:A6,B1:B6).

Calculate the result

Press Enter. The formula multiplies each value by its 1 or 0 flag, effectively adding only the included values to the total.

Use SUMPRODUCT with a 1 or 0 Helper Column

Easy Updates: You can change any 1 to a 0 at any time, and the total will instantly update to exclude that row's value.

Solution 2

Use SUMIF with Date-Based Inclusion

Calculate totals automatically based on whether a corresponding cell, such as a transaction date, is filled out.

Advanced Formulas Made Easy

Easily Handle Complex Data Calculations with WPS Spreadsheet

WPS Spreadsheet provides a seamless and highly compatible environment for utilizing advanced formulas like SUMIF and SUMPRODUCT. It helps you quickly calculate conditional totals with a clean, user-friendly interface.

1. Open your dataset in WPS: Launch WPS Office and open your workbook using the Spreadsheet module.
2. Input the formula: Click the cell for your total result and type =SUMPRODUCT or =SUMIF, utilizing the on-screen tooltip for guidance.
3. Select your ranges: Highlight your value ranges and criteria columns, then press Enter to calculate the final conditional result.

Fully compatible with Microsoft Excel formulas, ensuring SUMIF and SUMPRODUCT work identically.Lightweight installation with fast processing speeds for large datasets and complex calculations.Intuitive formula prompts and auto-complete features to prevent syntax errors.Completely free for standard daily data analysis and spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I exclude hidden rows from a normal SUM calculation?

Yes. If you want to exclude cells simply by hiding their rows, replace the standard SUM function with SUBTOTAL. Using the formula =SUBTOTAL(109, A1:A10) will automatically exclude any rows within that range that have been manually hidden.

How do I include cells based on a specific word like 'Include'?

You can use the SUMIF function to check for exact text matches. For example, the formula =SUMIF(B1:B10, "Include", A1:A10) will sum the numbers in column A only if the corresponding cell in column B contains the exact word 'Include'.

Why does my SUMPRODUCT formula return a #VALUE! error?

A #VALUE! error in SUMPRODUCT almost always means that the ranges you selected are not identically sized. Double-check that your value range (e.g., A1:A6) and your helper column range (e.g., B1:B6) have the exact same number of rows and columns.

Excel & Data Problems