SUMIF Negative Numbers in Google Sheets: Tips

To add only negative numbers in Google Sheets, use =SUMIF(B2:B7,"<0") when the values are in B2:B7; it returns their total, including the minus sign. The key is the criterion “<0”: it includes values below zero and excludes zero and positive values.

The basic SUMIF formula

SUMIF checks a range against a condition and, when that condition is met, adds the matching values. For a column of numbers, that same column can be both the range being checked and the values being added. With values in B2:B7 of 120, -35, 0, -18, 62, and -7, the formula below totals the three negative entries.

With the six example amounts in B2:B7, use =SUMIF(B2:B7,"<0"). The result is -60, because -35 + -18 + -7 = -60.

The second argument is a criterion written as text in quotation marks. The less-than sign followed by zero means “less than zero.” Because the formula adds the original values, the result remains negative. That is usually what you want for a net total, such as the combined amount of expenses or losses.

If your amounts are in another column, replace B2:B7 with that range. Keep the criterion in quotation marks. In Google Sheets, as in Excel, the function name is SUMIF; there is no separate SUMIF function specifically for negative numbers.

How SUMIF evaluates negative values

The general structure of SUMIF has a range to inspect, a criterion to apply, and an optional range to sum. When the sum range is omitted, Sheets adds the cells in the inspected range that meet the criterion. When a separate sum range is supplied, Sheets checks one column but adds corresponding cells from another.

For example, imagine a ledger with transaction labels in A2:A7 and amounts in B2:B7. To add negative entries, Sheets checks B2:B7 and sums matching cells from B2:B7. A negative number is numerically less than zero, regardless of whether it represents a refund, expense, or correction; SUMIF does not interpret the meaning of the entry.

Be precise about the boundary. The criterion “<0” excludes zero, while “<=0” includes it. If the range contains a zero balance and the goal is to total negative amounts only, keep the strict less-than condition. Positive values are not converted or subtracted by the function; they simply fail the test and are left out.

Sum negative amounts for one category

A common next step is to total negative entries for a particular category, such as travel or supplies. In this setup, put categories in A2:A7 and amounts in B2:B7. SUMIF can test each category against a label and add the amount from the same row only when both the category and negative-value condition are satisfied—but a single SUMIF handles only one criterion, so use SUMIFS for both conditions.

With categories in A2:A7 and amounts in B2:B7, to total negative amounts labeled Travel, use =SUMIFS(B2:B7,A2:A7,"Travel",B2:B7,"<0"). If the Travel amounts are -35, 62, and -7, the result is -42.

SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria. Each pair adds another requirement: the category must be Travel, and the amount must be below zero. This distinction matters because nesting two unrelated SUMIF calculations or trying to put two conditions in one criterion does not express the same row-by-row test as SUMIFS.

Use a cell for the threshold

A fixed zero criterion is easy to read, but a threshold stored in a cell can make a report adjustable. For example, someone reviewing unusually large credits or debits might want to change the cutoff without editing the formula. The comparison operator is text, so join it to the threshold cell with an ampersand.

With amounts in B2:B7 and a numeric threshold in D1, this adds amounts below the threshold in D1: =SUMIF(B2:B7,"<"&D1). If D1 contains 0, the result for the example amounts is -60. If D1 contains -10, the result is -53, from -35 and -18.

This is a strict comparison: an amount equal to the threshold is excluded. To include amounts equal to the threshold, change the comparison operator to “<=” inside the quoted criterion. Make sure the threshold cell contains a number rather than text that merely looks like a number.

SUMIF or SUMIFS?

Use SUMIF when one condition is enough, such as “add all values below zero” or “add values for the Travel category.” Use SUMIFS when the result must satisfy multiple conditions at once, such as a specific category and a negative amount, or a particular date range and a negative amount. SUMIFS is available in Google Sheets and is not a special add-on.

For a one-condition category total, with category names in A2:A7 and amounts in B2:B7, this adds every amount whose category is Travel: =SUMIF(A2:A7,"Travel",B2:B7). That includes positive and negative Travel values. To restrict the total to negative Travel entries as well, use the SUMIFS example above.

Notice the order in the one-condition example: the first range contains the values Sheets tests, while the third argument is the range it adds. For a negative-only total, the test range and sum range can both be the amount column. For categories, those ranges differ. Keep paired ranges the same size and aligned to the same rows so each category is matched to its amount.

Common problems and how to fix them

The result is zero. Check that the tested range contains actual numeric values and that the criterion is entered as “<0” with the comparison operator inside the quotation marks. Also confirm that the formula points to the rows containing the data.
Negative-looking values are ignored. Imported data may contain numbers stored as text, sometimes because of a leading apostrophe, spaces, or an export format. A cell displaying -25 is not necessarily a numeric value. Select a sample cell and inspect its contents, then convert the source data to numbers before relying on a numeric comparison.
The total includes zero. Verify that the condition is strictly less than zero, not less than or equal to zero. Zero is neither positive nor negative, so it should not be counted for a negative-only total.
The total is unexpectedly too large or small. Check for a range that includes headers, omits lower rows, or covers different row spans for the criteria and sum ranges. In a category formula, a misaligned sum range can add an amount from a different row than the matching category.
The formula shows an error after copying. Google Sheets formulas commonly use commas to separate arguments, as in the examples. Spreadsheet locale settings can affect separators and number formats, so if Sheets rejects a pasted formula, check the file’s locale under File > Settings.

Formatting a cell to display currency or a minus sign does not itself change its numeric value. Conversely, a text value can be made to look numeric through formatting but still fail a numeric condition. Diagnose the underlying cell value rather than relying only on how it appears on screen.

Keep the data and result easy to audit

For a dependable total, keep one transaction per row, use a dedicated amount column, and avoid mixing labels or explanatory notes into that column. Put a clear header above the data and make sure the formula range begins below it. If new rows are added frequently, allow for the expected range or update it as the ledger grows; a correctly written condition cannot include rows outside its referenced range.

It can also help to show the negative-only total beside the overall total, rather than replacing the source values. That makes it easier to compare the selected subset with the full ledger and spot a mistaken criterion. For a simple audit, filter the amount column to show values below zero and compare the visible entries with the records the formula is intended to include.

Remember that adding negative values gives a negative subtotal. If a report instead needs to display the size of the outflow as a positive number, that is a presentation choice, not a different definition of “negative.” Label the result clearly—for example, “Negative subtotal” or “Expense magnitude”—so readers do not confuse a positive magnitude with the signed net total.

Frequently Asked Questions

How do I sum only negative numbers in Google Sheets?
Use SUMIF with the amount range and the criterion “<0”. It adds numeric values below zero and leaves out zero and positive numbers.

Does SUMIF include zero when I use a negative criterion?
No. A strict less-than-zero condition excludes zero. Use a less-than-or-equal condition only if zero should be included.

Why does SUMIF return zero for negative values?
The cells may contain text rather than numbers, the referenced range may be wrong, or the criterion may not be entered correctly. Check the underlying cell values and the formula range.

Can I sum negative values for just one category?
Yes. Use SUMIFS with the amount range as the sum range, the category range and label as one condition, and the amount range with a below-zero condition as another.

How can I show the negative total as a positive number?
For a display that reports the magnitude rather than the signed subtotal, negate the SUMIF result. With amounts in B2:B7, =-SUMIF(B2:B7,"<0") returns 60 for the sample data. Keep the label clear so readers know this is a magnitude, not the signed sum.

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.