SUMIF Negative Values in Google Sheets: A Clear Guide

To add negative numbers in Google Sheets, use SUMIF with the criterion "<0". If the values are in B2:B10, the formula is =SUMIF(B2:B10,"<0",B2:B10); it returns the total of the negative values while excluding positive numbers, zeros, and blank cells.

Quick setup: sum negative values

In Google Sheets in a web browser, click the cell where you want the total, enter the formula, and press Enter. The first range tells Sheets which cells to test; the criterion says to include values less than zero; and the final range tells Sheets which values to add. When the test range and the values to add are the same, the third argument is optional.

For a practical setup, put numbers in column B and place the result in another cell, such as B11. With the values to check in B2:B10, you can use =SUMIF(B2:B10,"<0"). This shorter version returns the same result because Sheets adds the matching cells from the range it tested. Keeping the result outside the tested range also avoids accidentally including the total in later calculations.

To enter the formula:

Select the cell for the total.
Type the formula, including the quotation marks around the criterion.
Press Enter and check that the result is negative when the matching values are negative.

A negative total is normally the expected result: adding -35 and -18 gives -53. SUMIF does not turn expenses into positive amounts; it adds the numbers with their original signs.

How SUMIF evaluates the criterion

The general structure is =SUMIF(range,criterion,sum_range). The range is where Sheets looks for a match. The criterion defines what counts as a match. The optional sum_range contains the corresponding numbers to total. If sum_range is omitted, Sheets sums the matching cells in range itself.

The criterion "<0" means “less than zero,” so it matches negative numeric values but not zero. The quotation marks are required because the comparison operator and value are supplied as a criterion string. A criterion of "<=0" would include zero as well as negatives, which is a different calculation. Positive values are not included in either version.

If the comparison is made in one column but the numbers to add are in another, the two ranges should cover corresponding rows. For example, the test range might be B2:B20 and the sum range C2:C20. Sheets checks each row in B and, when its value meets the condition, adds the value from the same row in C. Matching range sizes helps make the result predictable and prevents confusing totals.

Worked example with a checkable total

Suppose B2:B7 contains 120, -35, -18, 60, -12, and 25. The negative entries are -35, -18, and -12. Their arithmetic total is -65. With these values in B2:B7, this formula returns -65: =SUMIF(B2:B7,"<0",B2:B7).

Use a small test table when you are unsure whether the formula is selecting the right cells. Manually identify which entries are below zero, add them separately, and compare that sum with the result. This simple check can reveal a range that starts too low, ends too early, or accidentally includes a heading. It also helps distinguish a formula problem from a number-formatting issue.

For an expense total shown as a positive magnitude rather than a negative balance, apply ABS to the SUMIF result. With the same values in B2:B7, =ABS(SUMIF(B2:B7,"<0",B2:B7)) returns 65. That changes the presentation of the answer, not the underlying source values. Use the signed result when direction matters, such as when comparing credits and debits; use the magnitude only when the report specifically calls for an unsigned expense amount.

Sum negative values for one category

SUMIF accepts one condition. If you need both a category match and a negative-value test, use SUMIFS, which can apply several conditions to corresponding ranges. For example, suppose A2:A7 contains Travel, Food, Travel, Travel, Food, and Travel, while B2:B7 contains -35, -18, 60, -12, -8, and 25. To add only negative Travel entries, the formula is =SUMIFS(B2:B7,A2:A7,"Travel",B2:B7,"<0"). It returns -47, because -35 plus -12 equals -47.

In this formula, the first argument is the range to sum. The next two arguments form the first condition pair: the category range and the text criterion. The final pair asks Sheets to check the amount range for values below zero. All the ranges cover rows 2 through 7, so each category and amount stay aligned. Change the text criterion to the exact category label in your sheet, including any spaces or punctuation.

This is a useful distinction: SUMIF is enough when one condition identifies the negative numbers. SUMIFS is appropriate when the result must satisfy multiple independent conditions, such as category, date, department, or status. If you only need the negatives from an amount column, adding unnecessary conditions makes the formula harder to maintain.

Use a cell for the threshold

You do not have to hard-code zero in the criterion. A cell can hold the threshold, which is useful when a report needs to switch between different cutoffs. Suppose B2:B7 contains the example amounts and E1 contains 0. With those cell locations, =SUMIF(B2:B7,"<"&E1,B2:B7) returns -65. The ampersand joins the less-than operator to the value in E1 to create a criterion such as less than zero.

This approach is also useful for thresholds other than zero. If E1 contains -10, the same formula totals values below -10, not all negative values. Be careful about the boundary: “less than -10” does not include -10 itself. If the boundary should be included, use a less-than-or-equal criterion instead. Put a clear label beside the threshold cell so another reader can tell what the formula is comparing against.

Common problems and how to fix them

A result of zero or an unexpectedly small total often means Sheets is not recognizing the entries as numeric values, or the formula is looking at the wrong range. Imported data can contain numbers stored as text, stray spaces, or a leading apostrophe. A cell may look like -35 but still be text, so a numeric comparison may not treat it as the number you expect. Check a few source cells, try entering a known negative number directly, and confirm that the range includes the actual data rows rather than headings.

Check the signs: A value displayed in parentheses may represent a negative number because of its number format, but a visual style alone does not change a positive value into a negative one. Inspect the underlying value or the formula bar.
Check the criterion: Use the quoted comparison criterion "<0". A criterion that tests text or uses the wrong comparison symbol can select different cells.
Check range alignment: If you use separate test and sum ranges, make sure they refer to corresponding rows and have compatible dimensions.
Check the output cell: If the result is present but hard to read, the cell may have a number format that hides the minus sign or displays negatives in parentheses.

Blank cells do not contribute to a numeric sum, and zero is not a negative number. If your sheet uses formulas that return empty text, or imported values have inconsistent formatting, clean up the source data rather than relying on appearances. Also check whether a filter or hidden row is misleading you: SUMIF evaluates the referenced range, including matching values in rows that may not be visible. Hiding a row does not, by itself, remove its value from SUMIF’s calculation.

Number formats and signed totals

Formatting changes how a value appears, not what SUMIF calculates. To review or change the result’s display in Google Sheets, select the cell and use Format > Number. You can choose a standard number or currency format, or use a custom number format when a report needs a particular presentation. A format may show negatives with a minus sign or parentheses; either way, the stored total remains negative.

That distinction matters in expense reports. A total of -65 formatted as currency could appear as a negative currency amount or with parentheses, depending on the selected format. If you want the report to show 65 as an expense magnitude, use an explicit calculation such as ABS rather than changing a number format and assuming it has reversed the sign. Keep the choice consistent across related totals so readers can tell whether figures represent signed balances or positive expense amounts.

SUMIF compared with related options

For a single condition, SUMIF is usually the most direct choice. SUMIFS handles two or more conditions, such as “this category and a negative amount.” A manual sum is fine for a quick one-off check but is easy to miss or break when rows are added. If the task is to count negative values rather than add them, a counting function is more suitable than SUMIF. Choose based on the question: total, count, or list of matching entries.

Another common misconception is that SUMIF is a special “sum only negative” function. It is a general conditional-sum function; the negative test is simply the criterion supplied to it. That flexibility means the same pattern can total positive values with a greater-than-zero condition, or include zero with a less-than-or-equal condition. The important part is choosing the exact boundary and applying it to the range that contains the values you intend to test.

Frequently Asked Questions

Does SUMIF include zero when I use a negative criterion?
No. The criterion "<0" matches values strictly below zero, so zero is excluded. Use a less-than-or-equal criterion if zero should be included too.

Why does my negative total show as a positive number?
Check whether the source values are truly negative and whether the formula uses ABS or another calculation that removes the sign. A number format can change how a negative is displayed, but it does not make the underlying value positive.

Can I sum negative values from one column using a different column as the test?
Yes. Put the test range in SUMIF’s first argument and the values to add in its third argument. Keep both ranges aligned row by row so a match adds the value from the corresponding row.

Why does SUMIF return zero even though I can see negative entries?
The entries may be stored as text, the referenced range may miss them, or the displayed minus sign may be part of text rather than a numeric value. Check the cell contents and the formula’s range, then test with a known numeric negative value.

Does SUMIF ignore hidden rows?
No. SUMIF evaluates values in its referenced range even when rows are hidden. If you need a calculation that changes based on visibility, use a method designed for visible rows rather than assuming SUMIF follows the current view.

Should I use SUMIF or SUMIFS?
Use SUMIF for one condition, such as “amount is below zero.” Use SUMIFS when several conditions must all match, such as a specific category and an amount 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.