To subtract with Excel’s SUM function, add the amount you are starting with and include each amount to deduct as a negative argument. For example, you can enter a starting balance and one or more expenses in a SUM formula. This is useful for balances, budgets, stock counts, and other calculations where you want to add and subtract values in one formula.
The examples below are for Excel for Microsoft 365 on Windows. The formulas also work in Excel for Mac and Excel for the web; the ribbon layout can vary slightly between platforms. Make sure the cells you plan to calculate contain the numbers you intend to use, and choose an empty cell for the result.
What You’ll Need
- A workbook in Excel for Microsoft 365, Excel for Mac, or Excel for the web.
- Numbers entered in cells, including a starting value and the values you want to deduct.
- An empty cell where Excel can display the result.
You do not need to enable a setting or install an add-in. SUM is a built-in Excel function. It adds numbers together, so the key is to represent the amounts being subtracted as negative values in the formula, or to subtract a SUM total from a starting amount within a SUM formula.
Step-by-Step
-
Set up the values clearly. Put the amount you are starting with in one cell and the amount or amounts to subtract in other cells. For example, you might put a starting balance in B2 and expenses in C2 and D2. Add labels in the row above, such as “Starting balance,” “Expense 1,” and “Expense 2.” Clear labels make it easier to check that the formula is using the right cells. For a list of deductions, keep the amounts together in one column so you can select the whole range.
-
Select the cell for the answer. Click an unused cell where you want the result to appear, such as E2. Keep the result separate from the values being calculated. If you place the result inside the input range, you may accidentally include the result in the calculation or create a circular reference. A circular reference occurs when a formula refers to its own cell, directly or through another formula.
-
Subtract one value by making it a negative SUM argument. In this example, B2 contains a starting balance of 500 and C2 contains an expense of 120. With the starting amount in B2 and the amount to deduct in C2, enter
=SUM(B2,-C2)in E2. Press Enter. Excel adds 500 and negative 120, so the result is 380. The minus sign immediately before C2 tells Excel to use that cell’s value as a negative number for this calculation. The value in C2 remains positive and unchanged in the worksheet. -
Include more than one separate deduction. You can add additional negative cell references when the deductions are in separate cells. In this example, B2 contains 500, C2 contains 120, and D2 contains 45. With those values in the stated cells, enter
=SUM(B2,-C2,-D2)in E2. Excel calculates 500 minus 120 minus 45, giving 335. Separate the arguments with commas, and put a minus sign directly before each reference you want to subtract. This approach is practical when there are only a few deductions and each has its own clearly identified cell. -
Subtract the total of a range. If you have several deductions listed together, you can total them inside SUM and make that total negative. For this example, B2 contains a starting amount of 500, and C2:C5 contains deductions of 120, 45, 25, and 30. With that layout, enter
=SUM(B2,-SUM(C2:C5))in E2. The inner SUM adds the deductions to make 220; the outer SUM adds that negative total to 500. The result is 280. This is helpful when a list grows, because you can add deductions in the range and update its endpoints if the list extends beyond row 5. -
Check the displayed result and the formula bar. After pressing Enter, click the result cell and look at the formula bar above the worksheet. Confirm that the formula begins with an equals sign, that each deduction has a minus sign, and that the references point to the intended cells. For a quick arithmetic check, total the deductions separately or calculate the result by hand for a small example. In the range example, 120 + 45 + 25 + 30 is 220, and 500 minus 220 is 280.
-
Copy the formula to other rows if needed. If each row has its own starting value and deductions, select the result cell and drag its fill handle (the small square at the lower-right corner) down the column. Excel adjusts relative references for each row. For example, a formula in row 2 that refers to B2 and C2 will normally adjust to B3 and C3 when filled into row 3. Check the first copied result before filling a long list. On Windows, Ctrl+D fills selected cells down when the formula cell is at the top of the selection; on Mac, Control+D is the usual Fill Down shortcut. In Excel for the web, dragging the fill handle is a straightforward option.
There is another way to use SUM if deductions are already recorded as negative numbers. For example, if B2 contains a positive starting amount and C2:C4 contains negative adjustments, SUM can add the cells directly. Do not use a minus sign in the formula for those adjustments as well: that would reverse their sign and add them back. Decide whether your worksheet stores deductions as positive amounts or negative amounts, then use one method consistently.
Common Mistakes
-
Leaving out a minus sign. SUM adds the values supplied to it. If a deduction cell contains a positive number and you include it as a normal argument, Excel adds it instead of subtracting it. Check that each positive deduction reference has a minus sign before it.
-
Subtracting a value that is already negative. If your worksheet uses negative numbers to represent expenses, putting another minus sign before one of those references changes the calculation: subtracting a negative has the effect of adding. Review the actual cell values, not just their labels or number formatting.
-
Using the wrong range endpoints. A range that starts or ends on the wrong row may omit a deduction or include a heading or unrelated figure. Select the range carefully and check its first and last cells. If new rows are added later, confirm that the formula covers them.
-
Typing a formula without the initial equals sign. Excel may treat the entry as text rather than calculate it. A formula must begin with an equals sign. If you see the formula characters in the cell instead of a result, check whether the equals sign is missing or the cell is formatted as Text.
-
Confusing display formatting with the stored value. A cell may display a rounded number or a currency symbol while holding a more precise underlying value. If the result differs slightly from hand arithmetic, increase the displayed decimal places or inspect the value in the formula bar before assuming the formula is wrong.
Tips
-
Label input columns and result columns. Clear headings such as “Opening amount,” “Deductions,” and “Remaining” make it easier for someone else to follow the calculation. Keep source values and formula results in separate columns.
-
Use a range for a growing list. When you have many deductions, grouping them in one column makes it easier to total them than entering a long series of individual cell references. If you regularly add new rows, consider converting the data to an Excel table using Insert > Table. Table formulas and ranges can adjust as rows are added, but check the formula and included rows after changing the layout.
-
Choose a consistent sign convention. Store every expense as a positive amount and subtract it in the formula, or store expenses as negative numbers and add them. Mixing these systems is a common reason a balance appears too high or too low. A heading or note can clarify which convention the sheet uses.
-
Use number formatting to make results readable. Select the result cells and choose a suitable option from Home > Number, such as Number or Currency. Formatting changes how a number appears; it does not change the underlying calculation. Avoid typing currency symbols directly into numeric entries if doing so causes Excel to treat them as text.
-
Keep formulas easy to audit. For only two or three deductions, individual references can make the logic obvious. For a longer list, a range is generally easier to maintain. Add a brief note beside a complicated calculation so another person can tell what is being subtracted and why.
Troubleshooting
The cell shows the formula rather than the answer: Confirm that the entry starts with an equals sign. Then select the cell and check Home > Number to see whether its format is Text. If it is, change the format to General, edit the cell (press F2 on Windows, or Control+U on Mac in many Excel configurations), and press Enter or Return. If Show Formulas is enabled, turn it off from the Formulas tab or press Ctrl+` on Windows. On Mac, the keyboard shortcut can differ by keyboard layout, so use the Show Formulas control on the Formulas tab if needed.
The result is too large or has the wrong sign: Check whether a deduction is already stored as a negative value, and check that it has not been subtracted twice. Verify the formula references against the labels and the values in the referenced cells. A result above the starting amount often means a positive deduction was added or a negative value was subtracted.
The result is zero or unexpectedly small: Look for blank cells, omitted range rows, or amounts stored as text. SUM ignores text in referenced cells, so a number that looks like a number but is stored as text may not be included. Select the input cells and check their alignment and number format; use Excel’s warning indicator or convert the values to numbers where appropriate. Then recalculate or re-enter the formula.
Excel displays an error: For #VALUE!, inspect the referenced cells for text or other unexpected entries. For #NAME?, check the spelling of SUM and make sure the formula begins correctly. If Excel rejects commas between function arguments, your regional settings may use semicolons as list separators; follow the separator Excel inserts when you type a function. Do not change a valid formula just because another computer uses a different regional setting.
Copied formulas use the wrong cells: Relative references move when you fill a formula down or across. Check the references in the copied cell and make sure they still point to the matching row. If a reference must remain fixed while copying, you can make it absolute by adding dollar signs to the column and row reference, but only do this when the calculation genuinely needs a constant cell.
Frequently Asked Questions
Can SUM subtract numbers directly?
SUM is an addition function, but it can subtract when you supply negative values. Put a minus sign before a positive cell reference in the formula, or use a negative total as an argument. The original positive value in the worksheet does not have to be changed.
Can I subtract a whole range with SUM?
Yes. Add the starting amount to the negative result of a SUM that totals the range of deductions. Check that the range includes all the intended cells and that the starting amount is not included among the deductions.
Is this different from using the minus operator?
The ordinary minus operator is direct for subtracting one value from another. SUM is useful when you want to combine addition and deductions in one function or total a range of amounts before subtracting it. Both approaches can return the same result when they use the same values.
Do I need to change the expense cells to negative numbers?
No. If expenses are stored as positive numbers, you can make them negative in the formula. Alternatively, store them as negative numbers and add them with SUM. Choose one approach and avoid reversing the sign twice.
Why does SUM ignore one of my deductions?
A referenced cell may contain text instead of a number, or it may not be included in the formula’s range. Check the cell contents and the range boundaries. SUM ignores text in a referenced range, even when the text looks like a numeric entry.
Will the formula work in Excel for Mac or the web?
Yes. SUM and the examples’ calculation logic work in Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. The interface and some keyboard shortcuts differ, but the formula itself does not.
Related
- How to Replace a Picture in an Excel Header
- How to Repair an Excel File in Windows 11
- How to Repair Excel Desktop App: Windows Steps and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel TEXT Function for Dates: Formats and ExamplesTo turn an Excel date into formatted text, use =TEXT(A2,"mmmm d, yyyy"), assuming A2 contains a real date;…
- Excel Formula for Age From Date of BirthThe simplest Excel formula for age from a date of birth is =DATEDIF(A2,TODAY(),"Y"), assuming the birth date is…
- Excel Grade Formula Using COUNTIF: Setup and ExamplesTo assign a letter grade with COUNTIF, put the score in B2, the grade cutoffs 60, 70, 80,…
- How to Filter Dates in Excel by Month, Year, or RangeTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the arrow…
- Excel LET Function: How to Name and Reuse CalculationsUse Excel’s LET function to give a name to a value or calculation, then use that name elsewhere…
- Excel Formula List in Hindi: Meanings, Uses and ExamplesAn Excel formula list in Hindi is a guide to common spreadsheet functions, their Hindi meanings, and examples…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.