Formula Errors

Fix #VALUE! Error When Summing Excel Column Excluding Specific Rows

Maira Mehtab Oct 19, 2026 868 views

Question details

The user needs to calculate the sum of an Excel column while omitting specific rows, but their current subtraction formula results in a #VALUE! error or an unexpected currency dash format.

How to Fix #VALUE! Error When Summing an Excel Column Excluding Specific Rows

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating a total column sum while intentionally leaving out specific cells within the selected data range.
Observed behavior: Using a formula like =SUM(F2:F35)-F17-F31 throws a #VALUE! error due to text or non-numeric data in the subtracted cells. In other instances, the formula result formats as '£-' when the underlying value evaluates to zero.

Before you start

Identify whether the specific rows you wish to exclude contain numeric values, text, or are completely blank, as this determines the most effective formula approach.

Solution 1Recommended

Use the SUM Function to Subtract Specific Rows

Prevent the #VALUE! error by using a secondary SUM function to handle the excluded cells instead of direct subtraction.

Directly subtracting a cell using the minus sign (-) will cause a #VALUE! error if the excluded cell contains text, an error value, or a blank space. Because the SUM function automatically ignores text, wrapping your excluded cells in their own SUM formula is a much safer calculation method.

Select the target cell

Click on the cell where you want the final calculated total to appear.

Input the nested SUM formula

Type the formula =SUM(F2:F35)-SUM(F17,F31), replacing the cell references with your actual data range and the specific rows you wish to exclude.

Apply the calculation

Press the Enter key on your keyboard to apply the formula and view the total.

Use the SUM Function to Subtract Specific Rows

Formula Tip: You can add as many excluded cells as you need inside the second SUM bracket by simply separating each cell reference with a comma.

Solution 2

Rely on SUM to Automatically Ignore Text Cells

If the rows you want to exclude do not contain numbers, you do not need to manually subtract them.

Solution 3

Fix the '£-' Display by Adjusting Number Formats

Resolve the issue where your formula successfully calculates but displays as a dash symbol with a currency sign.

Efficient Data Calculation

Sum Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides a robust and highly compatible platform for all your data calculation needs. Easily sum columns, safely exclude specific rows, and utilize built-in error checking to avoid common formula mistakes like the #VALUE! error.

1. Open your file: Launch WPS Spreadsheet and open the document containing your data.
2. Select the total cell: Click the specific cell where the final sum should be displayed.
3. Input the exclusion formula: Type the formula =SUM(F2:F35)-SUM(F17,F31) to calculate the total while safely ignoring text strings in the excluded rows.
4. View the result: Press Enter to instantly view your error-free calculation.

100% compatible with Microsoft Excel (.xlsx) formats and formulasIntelligent formula auditing to help locate calculation errors quicklyCompletely free, lightweight, and features a familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #VALUE! error when subtracting a cell in Excel?

The #VALUE! error occurs when you use direct mathematical operators (like the minus sign) on cells that contain text, empty strings, or other non-numeric data. Excel requires actual numbers for direct arithmetic and throws an error if it encounters incompatible characters.

How can I exclude multiple specific rows from a large sum?

The safest and most reliable method is to calculate the total range with one SUM function, and then subtract a second SUM function containing your excluded cells. For example, using =SUM(A1:A100)-SUM(A10,A25,A50) will subtract the selected cells without triggering errors if they happen to contain text.

Why is my SUM formula returning £- instead of a number?

A result of £- (or $-) means the formula has successfully calculated the underlying value to be exactly zero, but the cell is formatted with the Accounting number format. You can easily fix this by right-clicking the cell, selecting Format Cells, and changing the number format to General or Number.

Excel & Data Problems