SUMIF Negative in Google Sheets: Sum Values Below Zero

To sum negative numbers in Google Sheets, use =SUMIF(B2:B100,"<0") when the values are in B2:B100. The criterion "<0" tells SUMIF to include numbers below zero; the result will normally be negative too.

What SUMIF does with negative numbers

SUMIF adds numbers that meet a condition. For a negative-number total, the condition is simply “less than zero.” Google Sheets checks each cell in the range, includes numeric values below zero, and adds those values together. Positive numbers and zero do not meet the condition, so they are left out.

The negative sign in the result is expected. For example, if the matching entries are -12, -8, and -5, their sum is -25. That is the net total of the signed values, not the positive size of the loss. If you need to report how large the combined decrease is as a positive number, you can take the absolute value of the sum instead; the distinction matters for reporting and further calculations.

SUMIF is useful for totals such as refunds, expenses, stock changes, temperature changes, or any column where increases and decreases are recorded with positive and negative signs. It does not need a special “negative numbers” mode: the criterion performs the filtering.

How the formula is structured

SUMIF accepts a range to inspect, a criterion to test, and, optionally, a separate range to add. If the inspected range is also the range you want to total, the third argument can be omitted. In the opening example, Sheets both checks and adds the values in B2:B100.

The criterion is written as text in quotation marks. The comparison operator and zero form the criterion "<0". In a spreadsheet formula, the quotes are part of how the criterion is supplied; they do not mean that the cell values themselves must be text. A numeric cell containing -3 qualifies, while a cell containing 3 does not.

When your data uses one column for the condition and another for the amount, supply both ranges. With transaction types in A2:A100 and signed amounts in B2:B100, the following formula checks the amounts in column B and totals the matching values in column B: =SUMIF(B2:B100,"<0",B2:B100). This is equivalent to omitting the third argument in this particular layout, but makes the roles of the ranges explicit.

Be careful not to confuse the range being tested with the sum range. If you want to add amounts only when another column meets a condition, put that other column in the first argument and the amount column in the third.

Examples for common spreadsheet layouts

For a quick check, a short column is enough. With values in B2:B6—10, -4, 0, -7, and 3—the formula =SUMIF(B2:B6,"<0") returns -11, because -4 plus -7 equals -11. The zero is not negative, and the two positive values are excluded.

For a ledger, you may want the total of all negative entries while leaving the original data intact. With signed transaction amounts in C2:C500, use =SUMIF(C2:C500,"<0"). If the selected entries are -24.50, -10, and -5.25, the result is -39.75. Format the result as currency if appropriate; number formatting changes how it looks, not the underlying calculation.

For a positive measure of the total decrease, use ABS around the negative sum. With the same signed amounts in C2:C500, enter =ABS(SUMIF(C2:C500,"<0")). For the entries above, the displayed result is 39.75. Keep the ordinary negative total if later formulas depend on the signed balance; use the absolute value only when a positive magnitude is what you intend to show.

Sum negative amounts for a category

A common related question is how to total negative values for just one category, such as refunds for a particular product or expenses for one department. Use SUMIFS when there are multiple conditions: one condition can identify the category, while another selects values below zero.

With category names in A2:A100 and signed amounts in B2:B100, this formula totals only negative amounts for the category “Travel”: =SUMIFS(B2:B100,A2:A100,"Travel",B2:B100,"<0"). The sum range comes first in SUMIFS, followed by a criteria range and its criterion for each condition. The formula returns a negative total, or zero if no rows match both conditions.

For a single category condition without filtering for negatives, SUMIF is enough. With categories in A2:A100 and amounts in B2:B100, =SUMIF(A2:A100,"Travel",B2:B100) adds all Travel amounts, positive and negative. To select negatives as well as a category, choose SUMIFS rather than trying to combine two conditions in one SUMIF criterion.

Use SUMIF for one condition, such as values below zero or one category.
Use SUMIFS for two or more conditions, such as a category and a negative amount.
Make sure the criteria ranges and sum range cover corresponding rows. A range that starts on row 2 should not be paired with another that starts on row 1.

Choosing the right negative-value criterion

The condition "<0" includes every number less than zero, including decimals such as -0.01. It excludes zero. That is the right choice when the question is strictly “what is the total of the negative values?”

Sometimes you want to exclude tiny amounts caused by rounding or include values only below a threshold. For a threshold of -10, the criterion "<=-10" includes -10 and values lower than -10, but not -9.99. With values in B2:B100, use =SUMIF(B2:B100,"<=-10") to total that subset. Check whether your intended cutoff is inclusive: < excludes an exact match, whereas <= includes it.

To count negative entries instead of adding them, use COUNTIF. With values in B2:B100, =COUNTIF(B2:B100,"<0") returns the number of cells containing negative numbers. Counting can help verify a total, particularly when you expect a certain number of qualifying rows.

Common problems and how to fix them

The result is zero: Check that the values are numeric and genuinely below zero, and confirm that the formula points to the right cells. Numbers imported or entered with a leading apostrophe may be stored as text, so they may not behave like numeric values. You can inspect a few cells, remove unwanted text formatting, or convert the source data to numbers.
The result includes values you did not expect: Look for an incorrect range, especially if the formula includes a header row or unrelated rows. Confirm that the criterion is "<0", not "<=0", which also includes zeros.
The result is negative when you expected positive: SUMIF returns the signed total. Use ABS only if you specifically need the magnitude expressed as a positive number; do not change the sign just to make a result look more convenient if it represents a balance.
The formula reports a parse error: Check the quotation marks, parentheses, commas, and range references. In some Google Sheets locales, formulas use semicolons rather than commas as argument separators. If the comma version is rejected, use the separator required by your spreadsheet’s locale.
The category total is wrong: Make sure the category text matches the entries and that the category range lines up row-for-row with the amount range. Extra spaces or inconsistent category labels can make rows fail to match.

Blank cells and ordinary text entries do not count as negative numbers. A cell that looks like a negative number but is stored as text is a more likely cause of a missing value. Also check whether the data includes formulas that return numbers: SUMIF evaluates their numeric results, so a formula returning -2 can qualify just like a number typed directly into a cell.

SUMIF compared with alternatives

SUMIF is usually the simplest choice for one condition. SUMIFS is more suitable when the total depends on several conditions, such as category, date, and sign. Its syntax places the sum range first, then repeats a criteria-range and criterion pair for each additional test. Both functions work directly in Google Sheets and are also available in Excel, although menus and some locale settings can differ.

You can also filter the data visually with Google Sheets’ filter controls and inspect the negative rows, but that is a manual review rather than a reusable total formula. A formula updates as qualifying values change, which is generally more reliable for a report. If you only need a quick count rather than a sum, COUNTIF is the relevant alternative; it counts qualifying cells instead of adding their values.

For an ongoing summary, keep the source values in a consistent column and place the result outside the data range. This makes the formula easier to audit and reduces the chance that inserting rows or editing a table will cause the calculation to be misunderstood. If new entries are added beyond a fixed endpoint such as row 100, remember to extend the range or choose a range that accommodates future data.

Frequently Asked Questions

Can SUMIF add only negative numbers in Google Sheets?
Yes. Use the criterion "<0" for the amount range. With values in B2:B100, =SUMIF(B2:B100,"<0") adds only numeric entries below zero.

Why does SUMIF return a negative answer?
Because the values being added are negative. The result is their signed total. If you need the combined size of the decrease as a positive number, wrap the SUMIF calculation in ABS, while keeping in mind that this changes the sign of the reported result.

Does SUMIF include zero?
No. The criterion "<0" excludes zero. To include zero along with negative numbers, use a less-than-or-equal criterion, "<=0", after confirming that including zero fits the question you are answering.

Can I sum negative values for one category?
Yes. Use SUMIFS with the amount range as the sum range, a category range and category criterion, and the amount range again with the "<0" criterion. This requires each range to cover the same rows.

Can SUMIF work if negative values are stored as text?
It is designed to sum numeric values that meet the criterion, so text that merely looks like a number can be skipped or behave unexpectedly. Convert text-formatted values into numbers before relying on the total, then check that the negative entries are recognized as numeric.

Is the formula different in Excel?
The basic SUMIF formula and negative criterion also work in Excel. This page uses Google Sheets in a browser; the main differences a reader may encounter are interface paths and regional formula separators, not the basic logic of summing values 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.