SUMIF Negative Values in Google Sheets: Formula & Tips

To add only negative numbers in Google Sheets, use =SUMIF(B2:B100,"<0"), replacing B2:B100 with the range containing your numbers. The criterion "<0" tells the function to include values below zero and leave out zero, positive numbers, and text.

The Basic SUMIF Syntax

SUMIF checks a range against one condition and adds the matching values. Its general syntax is SUMIF(range, criterion, [sum_range]). The first argument is the cells to test, the second is the condition, and the optional third argument is the cells to add. Square brackets in this syntax indicate an optional argument; do not type the brackets into your formula.

When you want to add the negative numbers in the same cells you are checking, omit the third argument. For example, with values in B2:B100, use =SUMIF(B2:B100,"<0"). If the numbers to test and the values to add are in different columns, supply both ranges. The criteria range and sum range should correspond row by row.

What the Negative-Number Criterion Means

The criterion "<0" is a comparison: include a cell if its numeric value is less than zero. The quotation marks are required because the criterion combines a comparison operator with a value. In Google Sheets, the operator and zero are written together inside the quoted criterion.

This is not the same as asking for numbers that are less than or equal to zero. A cell containing exactly 0 does not meet "<0". If you intend to include zero as well, change the criterion to "<=0". Likewise, ">0" selects positive values only. These distinctions are useful when a zero balance should not count as a loss or expense.

Formatting does not change the test. A value displayed as currency, a percentage, or a date is still evaluated according to its underlying value. A real numeric entry of -25 qualifies even if the cell displays it as ($25.00); a positive 25 does not become negative just because a custom format or a minus sign in a label makes it look that way.

Worked Example: Add Negative Entries

Suppose B2:B6 contains -12, 7, -3, 0, and -4. With those values in B2:B6, use =SUMIF(B2:B6,"<0"). The formula adds -12, -3, and -4, returning -19. The positive 7 and zero are excluded.

The result remains negative because SUMIF adds the original values, not their absolute magnitudes. That is usually what you want for a net total of refunds, losses, or downward adjustments. If you instead want to report how large the negative amounts are as a positive number, that is a different calculation: the sum of -12, -3, and -4 has a magnitude of 19, but SUMIF itself returns -19.

To make the example easier to inspect, you can place a heading above the values and keep the formula outside the data range. Avoid including the formula cell in the range it sums; doing so can create a circular reference or cause confusing results if the layout changes.

Sum One Column Based on Negative Values in Another

Sometimes one column determines which rows qualify, while another holds the numbers you want to add. For example, suppose B2:B20 contains a signed adjustment and C2:C20 contains a separate amount to total. With the adjustment values in B2:B20 and amounts in C2:C20, use =SUMIF(B2:B20,"<0",C2:C20). Sheets checks column B for negative entries and adds the values from the corresponding rows in column C.

Pay attention to the order of the arguments. The first range is the test range; the last range is what gets summed. Reversing them can make the formula check the wrong cells or return an unexpected total. The two ranges should have the same number of rows so each qualifying test cell maps to its intended amount.

If both the test and sum range are the same column, the shorter two-argument form is clearer. If you want to sum negative values in C, for instance, test C directly rather than adding a separate sum range that is unnecessary.

Combine Negative Values with a Category

A common related task is totaling negative entries for one category, such as negative travel adjustments while ignoring negative entries in other categories. SUMIF handles one condition. For two or more conditions, use SUMIFS, which puts the sum range first and then pairs each criteria range with a criterion.

With categories in A2:A20 and signed amounts in B2:B20, use =SUMIFS(B2:B20,A2:A20,"Travel",B2:B20,"<0"). This adds amounts only where the category is Travel and the amount is below zero. If the category labels are in a different column or use different wording, update the matching range or criterion. SUMIFS is useful here because it can apply both conditions to the same row.

For a category selected from a cell, replace the typed category text with a cell reference. For example, if E1 contains Travel, the criterion can refer to E1 instead of spelling out Travel in the formula. This makes a summary table easier to reuse for several categories, while the negative-value condition stays in place.

Common Problems and How to Fix Them

Numbers stored as text: A cell that looks like -12 may have been imported or entered as text. Text values may not behave like numeric negatives in a comparison. Check whether the value is left-aligned unexpectedly, inspect the cell, or convert the imported data to numbers before relying on the total.
A formula returns zero: Confirm that the range is correct and that at least one cell contains a genuine number below zero. Check for an extra space or a different character in imported values, such as a typographic dash instead of the standard minus sign.
The result is too large or too small: Check whether the third argument is needed and whether it points to the intended sum range. Also check that the sum range lines up with the criteria range rather than including unrelated rows.
Zeros are being included or excluded unexpectedly: Use "<0" for strictly negative values and "<=0" when zero should qualify. A displayed value rounded to 0.00 may still be slightly negative or positive underneath, so increase the displayed decimal places if the result seems surprising.
Formula parse error: In many Google Sheets locales, commas separate function arguments, as in the examples here. Some spreadsheet locales use semicolons instead. If Sheets rejects a correctly structured formula, check the spreadsheet’s locale and use the argument separator it expects.

When values come from an import, look at a few individual cells before changing the formula. SUMIF can only make the comparison it is given; it cannot reliably infer that a text string with a dash should be treated as a numeric value. Correcting the source data often fixes several formulas at once.

SUMIF Compared with Other Options

For a single negative-number condition, SUMIF is usually the most direct choice. It is readable, works well for a normal table, and does not require a separate helper column. Choose SUMIFS when you need to add another condition, such as a category, account, or date range. The formulas have different argument orders, so remember that SUMIFS starts with the range to add.

If your aim is to count negative cells rather than total them, use COUNTIF instead. With values in B2:B100, the count formula is =COUNTIF(B2:B100,"<0"). It returns how many qualifying cells there are, not the combined amount. For a list of negative entries rather than a total, a filtering approach may be more suitable than a summing function.

A helper column is another option when the rule is more complicated or needs to be visible to coworkers. For example, a separate column can label each row as negative or not, after which a summary formula can use that label. This is not necessary for the basic case, but it can make a complex review process easier to audit.

Using the Formula in Google Sheets and Excel

These examples are for Google Sheets in a web browser. Enter a formula in a blank cell, begin with an equals sign, and press Enter. You can also select a cell and type the formula in the formula bar. If the spreadsheet uses a locale with semicolon separators, replace argument-separating commas with semicolons while keeping the criterion itself in quotation marks.

SUMIF is also available in Excel, and the basic negative-value logic is the same. The interface and some settings differ between the programs, and Excel may also use regional argument separators. If you share a file between Sheets and Excel, check that the formula is accepted under the receiving application’s locale and that the referenced ranges still match the data.

Frequently Asked Questions

How do I sum only negative numbers in a column?
Use SUMIF with the column or cell range as its first argument and "<0" as its criterion. For values in B2:B100, the basic formula is =SUMIF(B2:B100,"<0").

Does SUMIF include zero?
No. The criterion "<0" means strictly less than zero. To include zero, use a less-than-or-equal criterion instead.

Why does my negative SUMIF formula return zero?
Check that the referenced cells contain numeric negative values, not text, and that the range covers the right rows. Also check for imported dash characters, hidden spaces, or a criterion that was typed without the comparison operator.

Can SUMIF add negative values from one column based on another?
Yes. Set the first range to the cells that determine whether each row qualifies, use "<0" as the criterion, and set the optional sum range to the corresponding amounts. Ensure both ranges cover matching rows.

How can I sum negative values for just one category?
Use SUMIFS with the amount range first, then add a category condition and a negative-number condition. This totals only rows where the category matches and the amount is below zero.

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.