Excel’s SUM function already includes negative numbers when you add a range: their values reduce the total. To add only the negative numbers, use SUMIF with the criterion "<0", such as =SUMIF(B2:B8,"<0",B2:B8).
What it means to sum negative numbers
A negative number represents a value below zero. In a sum, it subtracts from the positive values rather than being ignored. For example, adding 12 and -5 gives 7. This is the same arithmetic Excel uses: a number’s sign affects the total, but it does not determine whether the number is included.
That distinction answers two common interpretations of “sum negative numbers”:
- Add a range that contains negative values: Use
SUM. It adds the numeric values in the range, so negative entries lower the result. - Add only values below zero: Use
SUMIFwith a less-than-zero criterion. Positive values and zero are left out.
These are different calculations. If a range contains both sales and refunds, for instance, a normal sum gives the net amount. A sum of negative values alone gives the total of the refunds as a negative number. Which result is useful depends on the question you are trying to answer.
How SUM handles negative values
SUM adds numeric values in its arguments. When its argument is a cell range, it ignores blank cells and text entries, but it includes both positive and negative numbers. It also includes zero, which does not change the result. If a referenced cell contains an Excel error, that error can make the formula return an error instead of a total.
Suppose B2:B8 contains -12, 8, -3.5, 0, -4.5, 10, and a blank cell. With those values in that layout, enter =SUM(B2:B8). The result is -2: the negative entries total -20, and the positive entries total 18, so -20 + 18 = -2. The blank cell contributes nothing.
You do not need a special formula just because some entries are negative. If the goal is a net total, using SUM is usually the clearest approach. A negative result simply means the negative values outweigh the positive ones in the range.
Sum only values below zero with SUMIF
Use SUMIF when you want to include cells only when they meet one condition. Its general form is SUMIF(range, criteria, [sum_range]): range is checked against the criterion, and sum_range supplies the values to add. The square brackets in this description mean that the argument is optional; do not type the brackets into the formula.
With the values in B2:B8 as described above, enter =SUMIF(B2:B8,"<0",B2:B8). The result is -20, because it adds -12, -3.5, and -4.5 while excluding 8, 0, 10, and the blank. The criterion is text in quotation marks: "<0" means “less than zero.”
Because the cells being tested are also the cells being added, the last range is optional here. With the same B2:B8 layout, =SUMIF(B2:B8,"<0") also returns -20. Including the sum range can make the intent easier to see and is necessary when the condition and the values to add are in different columns.
For example, assume A2:A8 contains transaction types and B2:B8 contains their amounts. If the amount column holds both positive charges and negative refunds, a criterion on B can select negative amounts directly. If instead you need to sum amounts based on a label such as “Refund,” use the label column as the condition range and the amount column as the sum range.
Work through a small example
Enter these amounts in B2:B7: -18, 42, -7, 0, -5, and 11. This makes it easy to compare a net total with a negative-only total. With those values in B2:B7, =SUM(B2:B7) returns 23: the negative entries total -30 and the positive entries total 53, producing 53 – 30 = 23.
To find the total of the negative entries in the same B2:B7 layout, use =SUMIF(B2:B7,"<0",B2:B7). It returns -30. If you want to report the amount of those losses as a positive magnitude instead, the calculation is different: the negative-only sum is -30, while its magnitude is 30. Be clear about whether your report should preserve the accounting sign or show an unsigned size.
This example also shows why zero needs no special handling for a less-than-zero test. Zero is not less than zero, so SUMIF excludes it automatically. A positive number also fails the condition, while every numeric value below zero meets it.
Sum negatives when another condition matters
Sometimes the question is not simply “What are all the negative amounts?” You might want negative amounts for one department, date range, or category. SUMIFS can apply multiple conditions to a sum range. Its basic pattern is SUMIFS(sum_range, criteria_range1, criteria1, ...); unlike SUMIF, the sum range comes first.
Assume A2:A8 contains department names, B2:B8 contains transaction amounts, and C2:C8 contains dates. To add only negative amounts for the Sales department, with the same row alignment in all three ranges, use =SUMIFS(B2:B8,A2:A8,"Sales",B2:B8,"<0"). This adds an amount only when its department is Sales and its value is below zero. The result depends on the actual entries in those cells.
For multiple conditions, make sure the ranges cover the same rows. A sum range of B2:B20 paired with a criteria range of A2:A19 does not describe matching records and can produce an error. For date conditions, use real Excel date values rather than text that merely looks like a date. You can also use cell references for criteria, which makes a report easier to reuse when the selected department or threshold changes.
What happens with filtered or hidden rows?
A regular SUM or SUMIF does not automatically limit its calculation to rows visible after filtering. It can still include records hidden by an AutoFilter. This surprises people who filter a transaction table to show refunds and expect a normal total formula to change with the visible rows.
For a visible-row subtotal, Excel provides SUBTOTAL. If B2:B8 contains amounts and you have filtered the rows you want to inspect, =SUBTOTAL(9,B2:B8) adds the visible numeric values in that range; the result depends on which rows remain visible. Function number 9 includes manually hidden rows, while function number 109 excludes them as well. These options do not, by themselves, select only negative amounts.
A practical no-formula approach is to filter the amount column to show values less than zero, then use a subtotal that responds to the filter. In Microsoft 365 for Windows, select the table or data range and choose Data > Filter, then use the amount column’s filter menu to select a number filter less than 0. If you need a reusable total that combines visibility and other criteria, check the result carefully: ordinary SUMIF is not a visible-rows-only calculation.
Common reasons a negative value is missed
If a result seems wrong, check the data as well as the formula. A cell can look like a negative number while being stored as text, especially after importing data from a website, CSV file, or another system. SUM and SUMIF may not treat text that resembles a number as a numeric amount. A leading apostrophe, an unexpected space, or an imported minus character can be a clue.
Other useful checks include:
- Confirm the formula ranges. Make sure they cover every intended row and that criteria and sum ranges align.
- Check for errors. A cell containing an error such as
#VALUE!can affect a calculation. Find and resolve the source rather than hiding it without understanding it. - Check the sign in the underlying value. A custom number format can display values in parentheses, but formatting alone does not turn a positive value into a negative one.
- Check whether the value is actually zero. A formula may return a very small value that is rounded to display as 0. Increase the displayed decimal places to inspect it.
To inspect a cell, select it and look at the formula bar and the Number group on the Home tab. Formatting can change how a numeric value appears, but it does not change the stored number. Conversely, changing a display format will not reliably convert text into a number; clean or convert imported data when necessary.
Sign, formatting, and interpretation
Excel stores numeric values independently of their display format. A value of -25 may appear as -25, (25), or with a currency symbol, depending on the chosen number format. All of those displays can represent the same negative numeric value, and a sum uses the underlying value rather than the way it looks.
Parentheses are commonly used in financial reports to indicate negative amounts, but they can also be literal text in imported data. If a cell contains text such as “(25)” rather than a numeric negative 25, a sum may ignore it. Do not infer the data type from appearance alone. Check whether Excel recognizes the entry as a number before relying on the total.
Also distinguish a negative-only sum from a sum of absolute values. The former preserves the minus sign and describes a net contribution below zero; the latter describes the combined size without regard to sign. For everyday worksheets, it is often clearer to preserve signs in the calculation and apply a separate presentation choice only when a report specifically needs magnitudes.
Frequently Asked Questions
Does Excel SUM include negative numbers?
Yes. SUM includes numeric negative values in a referenced range, and those values reduce the total. Use SUMIF with a less-than-zero criterion only when you want to add negative values without including positives.
How do I sum only negative numbers in Excel?
With the values in B2:B8, use =SUMIF(B2:B8,"<0",B2:B8). The formula returns the total of the entries below zero and excludes zero, positive values, and blanks.
Why is Excel not adding a negative number?
The entry may be stored as text rather than as a numeric value, or the cell may be outside the formula range. Check the underlying cell, the formula’s references, and whether an imported minus sign or extra character is present.
How do I sum negative numbers in one category?
Use SUMIFS when the negative-value test must be combined with another condition. Put the amount range first as the sum range, then add matching criteria ranges and criteria, such as a category name and a less-than-zero test.
Will SUMIF ignore rows hidden by a filter?
No. SUMIF normally includes matching rows even if a filter hides them. For totals of visible rows, use a filtering workflow with SUBTOTAL, and remember that SUBTOTAL alone does not apply a negative-only condition.
Related
- Text Excel File: How to Open, Import, and Save Data
- Test Excel Data: Practical Checks for Accuracy and Errors
- Excel PivotTable Testing: Checks, Steps, and Common Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- CONCAT in Excel: Join Text and NumbersYes. Excel’s CONCAT function can join numbers with text or other values, but it does not preserve a…
- Switch Excel Formulas to Values: Methods and TipsTo switch formulas to fixed values in Excel for Microsoft 365 on Windows, select the formula cells, press…
- XLOOKUP Showing Formula Instead of Result?If XLOOKUP displays the formula itself instead of a result, first turn off Show Formulas on the Formulas…
- Gross Margin Excel Calculation: Formula and ExamplesWith revenue in B2 and cost of goods sold (COGS) in C2, calculate gross margin in Excel with…
- Excel IFS in Multiple Cells: Apply Logic Across RangesTo use IFS in multiple cells, write the formula for the first row, then copy or fill it…
- Split a Cell in Excel: Text to Columns, Formulas, and MoreYes. In Microsoft Excel, select the cells and choose Data > Text to Columns to split their contents…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.