How to exclude cells in a column from sum in Excel?
When working with data in Excel, you may often find yourself needing to calculate the sum of a range while intentionally skipping certain cells – for instance, if those cells contain outliers, errors, irrelevant data, or specific values you want to leave out of an aggregate. As shown in the screenshot below, imagine you have a column of numbers but want to sum all except for the values in cell A3 and A4. This article will comprehensively demonstrate various practical methods to sum a range in Excel while excluding one or more specified cells, helping ensure you get precisely the results you’re after for financial analysis, reporting, or inventory management tasks that require selective calculations.
- Exclude cells in a column from sum with formula
- VBA Code – Programmatically sum a range, skipping/excluding specified cells
- Excel Formula – Use SUMIF/SUMIFS to include only values that do not match exclusion criteria
- Excel Formula – Use FILTER function in newer Excel versions to filter out excluded cells before summing
Exclude cells in a column from sum with formula
Using simple arithmetic within the SUM formula, you can directly exclude unwanted cells in your calculation. This approach is suitable for quick calculations when you have a small number of exclusions to handle. Follow these steps:
1. Select a blank cell to display the summing result, and enter the following formula in the Formula Bar, then press Enter to calculate the sum while excluding specific cells. For example:
=SUM(A2:A7)-SUM(A3:A4)
Explanation & Tips:
- The SUM(A2:A7) calculates the entire range, while SUM(A3:A4) subtracts the values of excluded cells. This works best when the cells to exclude are continuous.
- You can mix and subtract multiple exclusion cells easily if they are non-adjacent. For instance, to exclude A3 and A6 from the range, adjust the formula as follows:
=SUM(A2:A7)-A3-A6
- If the exclusions are scattered or numerous, manually listing each excluded cell can make formulas longer and harder to manage.
- Be careful with cell references: if your data or range changes, update the formula accordingly to avoid errors.
VBA Code – Programmatically sum a range, skipping/excluding specified cells
For scenarios where you have many exclusions or need to repeat the process often, using a VBA macro provides flexibility and automation. With VBA, you can sum a specified range and exclude any number of cells, whether continuous or discontinuous, by programmatically defining them. This method is suitable for users comfortable with the VBA environment and looking to streamline more complex exclusion logic.
Precautions: VBA macros can modify your workbook. Always save your work before running new code. Macros must be enabled for the above to run.
1. Go to Developer Tools > Visual Basic to open the VBA editor. In the Project window, right-click your workbook, select Insert > Module, and paste the following code into the module:
Sub SumWithExclusions()
Dim sumRange As Range
Dim excludeCells As Range
Dim cell As Range
Dim result As Double
Dim xTitleId
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set sumRange = Application.InputBox("Select the range to sum", xTitleId, Type:=8)
Set excludeCells = Application.InputBox("Select cells to exclude (use Ctrl+Click to select multiple)", xTitleId, Type:=8)
result = 0
If Not sumRange Is Nothing Then
For Each cell In sumRange
If Not Application.Intersect(cell, excludeCells) Is Nothing Then
' Skip excluded cells
Else
result = result + cell.Value
End If
Next
MsgBox "The sum excluding specified cells is: " & result, vbInformation
Else
MsgBox "No range selected.", vbExclamation
End If
End Sub2. Click 
Run in the VBA window or press F5 to execute the macro. A dialog will prompt you to select the full range to sum, then select the cells to exclude (hold Ctrl to select multiple). The macro will display the result in a message box.
- If you select cells in error, re-run the macro. The process is interactive and flexible.
- Macros are best for routine tasks or complex criteria-based exclusions.
Excel Formula – Use SUMIF or SUMIFS to include only values that do not match exclusion criteria
For more advanced or logic-based exclusions, you can use the SUMIF or SUMIFS functions. These formulas work well when your exclusions are based on value, criteria, or when you have a list of values to avoid.
Example – Exclude Based on a Specific Value
1. If you want to sum A2:A7 but exclude the value '16', enter the following formula in your target cell (for example, in cell B1):
=SUMIF(A2:A7,"<>16")This formula adds up all values in the range A2:A7 except those equal to 16.
2. After typing the formula, press Enter. You can copy or adjust the range/cell references as needed.
Example – Exclude All Cells Matching a Cell Value
Suppose cell C1 contains the value you wish to exclude from the sum:
=SUMIF(A2:A7,"<>"&A3)Update C1 as needed, and the formula will dynamically exclude all matching values.
- For multiple exclusion criteria or more complex rules, consider using SUMIFS in combination with helper columns or arrays. However, SUMIF/SUMIFS works best when exclusions are based on specific and consistent criteria, not arbitrary cell positions.
- If your range contains text or blank cells, SUMIF automatically ignores them; ensure this is the intended behavior.
Excel Formula – Use FILTER function (new Excel versions) to filter out excluded cells before summing
If you are using Excel for Microsoft 365 or Excel 2021 and later, the FILTER function enables dynamic and flexible exclusion of cells before applying SUM. This is especially useful for large datasets or varying exclusion criteria.
Example – Exclude Specific Values (e.g.,16 and13)
1. Enter the following formula in your target cell (e.g., B1):
=SUM(FILTER(A2:A7,(A2:A7<>16)*(A2:A7<>13)))This sums all values in A2:A7, except those equal to 16 and 13. The FILTER function creates an array that only includes cells not equal to those values, and then SUM adds them up.
2. Press Enter. The calculation will dynamically update if exclusions or source data change.
- To exclude values dynamically based on a list (e.g., the exclusion list is in C2:C4):
=SUM(FILTER(A2:A7,ISNA(MATCH(A2:A7,C2:C4,0))))This formula excludes any value in A2:A7 that matches any value in C2:C4. Simply update your exclusion list in column C, and the formula result updates automatically.
- The FILTER-based approach is recommended for users working with the latest Excel versions seeking dynamic, scalable exclusion logic.
- If you receive a #CALC! error, check that after all exclusions at least one value remains in the range; otherwise, FILTER returns an error.
In summary, Excel provides several practical solutions for summing a range while excluding specific cells or values. Simple formulas suit fast, small exclusions, while SUMIF/SUMIFS and FILTER support more flexible, condition-driven scenarios. VBA is ideal when exclusions are many, varied, or require automation. Always double-check cell references and formula adjustments when changing your source data. If you encounter errors, verify ranges or exclusion lists and try reapplying your formulas or rerunning the macro.
Related articles:
- How to exclude certain cell or area from printing in Excel?
- How to exclude values in one list from another in Excel?
- How to find minimum value in a range excluding zero value in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in