SUMIF Negative Values in Google Sheets: Formula Help

To total negative numbers in Google Sheets, use SUMIF with the criterion "<0". If the values are in B2:B100, the formula is =SUMIF(B2:B100,"<0",B2:B100); it adds only numbers below zero and returns a negative total.

How SUMIF handles negative numbers

SUMIF checks a range against one condition, then adds values from a sum range for the rows that meet that condition. Its syntax is SUMIF(range, criterion, [sum_range]). The first range is where Sheets looks for a match; the optional sum range is where it gets the numbers to add. If you leave out the sum range, Sheets adds matching numeric cells from the first range itself.

In the opening example, B2:B100 serves as both the range to check and the range to sum. The criterion is text enclosed in quotation marks: "<0". It tells Sheets to include values strictly less than zero. The formula returns a negative number when the selected values are negative; that is expected, not an error. For example, adding -25, -40, and -15 produces -80.

This is useful for totaling expenses or losses stored as negative amounts, finding refunds represented with a minus sign, or checking how much a set of adjustments reduces a balance. The formula does not change any source values; it calculates a result in the cell where you enter it.

Example: total negative amounts in a column

Suppose B2:B6 contains -25, 40, -12, 0, and -8. With the amounts in B2:B6, use =SUMIF(B2:B6,"<0",B2:B6). The result is -45 because the formula adds -25, -12, and -8. It leaves out 40 because it is positive, and 0 because zero is not less than zero.

Enter the formula in an empty cell, such as B8, rather than in the range being summed. If you place it inside the data range, the formula can include itself in its calculation and cause a circular reference. Keep the formula cell outside the source range, or adjust the ranges so they do not include the result cell.

If you want the size of the negative total shown as a positive amount, negate the result. With the same values in B2:B6, use =-SUMIF(B2:B6,"<0",B2:B6); the result is 45. This changes only the displayed calculation, not the underlying entries. It is a presentation choice: a signed balance is commonly shown as -45, while a report of expense magnitude might show 45.

Sum negative values only for a category

A common need is to add negative amounts from one category while ignoring positive amounts in that same category. Assume A2:A8 contains category names and B2:B8 contains amounts. If Travel entries in B2:B8 are -120, 50, and -35, and other rows belong to different categories, the formula =SUMIFS(B2:B8,A2:A8,"Travel",B2:B8,"<0") returns -155.

Here, the sum range is B2:B8. The first condition requires the corresponding category in A2:A8 to equal Travel; the second requires the amount in B2:B8 to be below zero. SUMIFS is appropriate because it supports multiple conditions. In Google Sheets, its argument order is the sum range first, followed by pairs of criteria ranges and criteria.

If there is only one condition, such as summing every Travel amount whether positive or negative, use SUMIF. With categories in A2:A8 and amounts in B2:B8, the formula =SUMIF(A2:A8,"Travel",B2:B8) adds all amounts on Travel rows. That distinction matters: a category condition alone does not mean the amounts must be negative.

Choose the right negative-value condition

The criterion depends on exactly what you mean by negative. These common choices are not interchangeable:

"<0" matches values below zero and excludes zero.
"<=0" matches negative values and zero.
">0" matches positive values and excludes zero.
"<-10" matches values smaller than -10, such as -11, but not -10.

For example, with numbers in B2:B20, use =SUMIF(B2:B20,"<=0",B2:B20) if zeros should be included in the matching set. Including zero usually does not change a sum, since adding zero contributes nothing, but it can matter when you are checking which rows qualify or adapting the condition for another calculation.

For a threshold stored in a cell, join the comparison operator to the cell reference. If B2:B20 contains amounts and D1 contains the threshold 0, use =SUMIF(B2:B20,"<"&D1,B2:B20). This produces the same below-zero test while letting you change the threshold in D1. The ampersand joins the text operator and the cell value to create the criterion.

Keep the criteria range and sum range aligned

When the test and the numbers to add are in different columns, make sure each row in one range corresponds to the same row in the other. For example, if A2:A50 contains dates and B2:B50 contains amounts, using B2:B50 as the negative-value criteria range and C2:C50 as the sum range would test one set of rows but add another column. That may be intentional, but it is often a range-selection mistake.

For the category example, A2:A8 holds labels and B2:B8 holds amounts, so the criteria range for the category is A2:A8 and the sum range is B2:B8. The ranges should cover the same number of rows. A formula with mismatched range sizes may return an error or an unintended result, so check both the starting row and the ending row when editing a copied formula.

For a straightforward negative total, using the same range for both arguments is the clearest option. With amounts in B2:B100, =SUMIF(B2:B100,"<0",B2:B100) tests and sums those same cells. If you later move the data or add rows, update the range consistently. You can also use whole-column references, but bounded ranges can be easier to inspect in a small sheet.

Common reasons the formula returns zero

If the result is 0 when you expect a negative total, first confirm that the source values are numeric. A cell can display characters such as -25 while actually storing text, especially after copying data from a website or importing a file. A text value may not behave like the numeric value needed for a numeric comparison and sum. Check whether the values align as numbers in the sheet, and try entering a known negative value directly into an unused cell as a comparison.

Also check the criterion spelling and the selected cells. The comparison must use a less-than sign followed by zero, as in "<0". A criterion of ">0" selects positive values instead. If a formula is in a different locale, Google Sheets may expect semicolons rather than commas between function arguments. The exact separator depends on spreadsheet locale settings; if a formula with commas is rejected, check the file’s locale and use the separator Sheets expects.

Other useful checks include:

Make sure the negative numbers are within the referenced range, not just visible elsewhere on the sheet.
Check that a filter or hidden row is not confusing your visual review. SUMIF does not mean “sum only the currently visible rows”; it evaluates the referenced cells against its criterion.
Look for leading apostrophes or imported text formatting that may leave minus-prefixed entries stored as text.
Verify that your result cell is outside the source range to avoid a circular reference.

SUMIF compared with SUMIFS and related options

Use SUMIF when there is one condition, such as “amount is below zero” or “category is Travel.” Use SUMIFS when the total must satisfy two or more conditions, such as “category is Travel and amount is below zero.” Both functions are built into Google Sheets and are suited to ordinary conditional totals.

A filter can help you inspect negative rows, but it is not a substitute for a reusable total formula. Filtering changes which rows are displayed; a SUMIF formula calculates against its stated range and condition. If your goal is to create a separate list of matching rows rather than a single total, use a filtering approach instead of adding them with SUMIF.

For a simple total of negative numbers, SUMIF is generally the most direct option. Avoid adding extra functions unless the output needs them. If you need a positive magnitude, negating the SUMIF result is straightforward. If you need a category, date, or status condition as well, use SUMIFS and make each condition explicit.

Formatting and interpreting the result

A negative result may display as -155, in parentheses, or with a currency symbol depending on the cell’s number format. Formatting changes how the result appears, not the arithmetic. To change the display, select the result cell and use the number-format options on the Google Sheets toolbar, or choose Format > Number and select an appropriate format.

Be clear about whether a report should show the signed total or the absolute size of the negative entries. If the data represents account movements, preserving the minus sign often communicates direction. For a report that labels the value as total expense magnitude, a positive display may be easier to read, but label it clearly so readers do not mistake it for income or a positive balance.

It is also worth distinguishing a sum from a count. SUMIF adds matching values; it does not tell you how many negative entries there are. If you need a count of cells below zero in B2:B100, use =COUNTIF(B2:B100,"<0"). With -25, 40, -12, 0, and -8 in B2:B6, this count is 3, while the corresponding negative sum is -45.

Frequently Asked Questions

How do I sum all negative numbers in Google Sheets?
With the values in B2:B100, enter =SUMIF(B2:B100,"<0",B2:B100) in an empty cell. It returns the sum of entries less than zero.

Why is my SUMIF negative formula returning zero?
Check that the range includes the entries and that they are numeric rather than text. Also confirm the condition is "<0", not ">0", and that the formula uses the argument separator expected by your spreadsheet locale.

How do I sum negative numbers for one category?
With categories in A2:A8 and amounts in B2:B8, use =SUMIFS(B2:B8,A2:A8,"Travel",B2:B8,"<0"). Replace Travel with the category you need.

How can I show the negative total as a positive number?
With the values in B2:B100, use =-SUMIF(B2:B100,"<0",B2:B100). The minus sign in front reverses the sign of the returned total; it does not alter the source data.

Does SUMIF include zero with the criterion less than zero?
No. "<0" matches only values below zero. Use "<=0" if zero should also qualify, although adding zero does not change the numerical sum.

Can SUMIF add only visible negative rows?
Not by itself. SUMIF evaluates the cells in its specified range, including rows hidden by a filter. If the total must respond only to visible rows, use a method designed around filtered or visible data rather than assuming the filter changes SUMIF‘s range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.