SUMIF Negative Values in Google Sheets: Examples

If your values are in A2:A7, enter =SUMIF(A2:A7,"<0") to add only the negative numbers; for example, -12, 8, -5, 0, -3, and 10 total -20. In Google Sheets, the criterion “<0” means “less than zero,” so positive values and zero are excluded.

How SUMIF works with negative numbers

SUMIF adds values that meet one condition. Its syntax is SUMIF(range, criterion, [sum_range]): the range contains the cells Sheets checks, the criterion describes which cells qualify, and the optional sum_range contains the values to add. If you leave out sum_range, Sheets adds qualifying cells from range itself. That is why the short formula above works when the numbers you are checking are also the numbers you want to total.

The criterion is written as text in quotation marks. For negative values, use “<0” rather than typing a minus sign alone. The comparison means each cell must be numerically smaller than zero. It includes values such as -0.01 and -125, but it does not include zero, positive numbers, or blank cells.

In a standard Google Sheets locale, formulas use commas between arguments. Some spreadsheet locales use semicolons instead, depending on regional settings. If Sheets reports a formula parse error despite the formula looking correct, check the spreadsheet’s locale under File > Settings and use the argument separator expected by that locale.

A basic negative-values example

Suppose A2:A7 contains these amounts, one per row: -12, 8, -5, 0, -3, and 10. With those values in A2:A7, enter =SUMIF(A2:A7,"<0") in another cell. Sheets selects -12, -5, and -3, then adds them: -12 + -5 + -3 = -20. The result remains negative because the formula returns the signed total, not the absolute size of the losses.

You can use the same pattern on a longer column. If transactions run from D2 through D500, use =SUMIF(D2:D500,"<0"). The range should cover the actual amount cells, including every row you want checked. Avoid including a text heading in the range when possible; a heading will not normally qualify as a negative number, but a clean data range makes formulas easier to understand.

A useful way to read the basic formula is: “Look in this range, keep entries below zero, and add those entries.” SUMIF does not change the source values or hide rows. It calculates a result in the cell containing the formula, and the original amounts stay as they are.

Sum negative amounts for one category

Many real sheets have one column for a label and another for an amount. For example, A2:A7 might contain Travel, Supplies, Travel, Meals, Travel, and Supplies, while B2:B7 contains -120, -40, -45, -30, 15, and -10. To total negative amounts for Travel, use SUMIFS, which supports more than one condition. With categories in A2:A7 and amounts in B2:B7, enter =SUMIFS(B2:B7,A2:A7,"Travel",B2:B7,"<0"). It returns -165: the two Travel amounts below zero, -120 and -45.

SUMIF handles one criterion, while SUMIFS is the practical choice when you need both a category match and a negative-number test. In the SUMIFS formula, the first argument is the range to add. The next pair says that the category range must equal Travel, and the final pair says that the amount range must be less than zero. Each criteria range should align with the sum range in size and row position, so B2:B7 is paired with A2:A7 and B2:B7—not with ranges of different lengths.

For a category typed in a cell rather than hard-coded into the formula, put the desired category in D1. With categories in A2:A7, amounts in B2:B7, and the selection in D1, use =SUMIFS(B2:B7,A2:A7,D1,B2:B7,"<0"). Changing D1 to Supplies or Meals updates the answer without editing the formula. This is convenient for a small report or a dropdown-driven summary.

Change the negative-value condition

Sometimes “negative” means more than simply below zero. If you want only amounts less than -100, the criterion can be “<-100”. With amounts in B2:B20, use =SUMIF(B2:B20,"<-100"). This adds values such as -125 and -250, but not -80, -100, or positive amounts. Because the comparison is strict, a value equal to -100 does not qualify.

If you want to include -100 as well, use a less-than-or-equal criterion. With the same B2:B20 range, enter =SUMIF(B2:B20,"<=-100"). It includes -100 and any smaller number. Such thresholds can help isolate unusually large charges, returns, or adjustments, but choose the cutoff to match what the data represents rather than treating a threshold as a universal rule.

To count qualifying entries rather than add their values, use COUNTIF. With amounts in B2:B20, enter =COUNTIF(B2:B20,"<0"). This returns the number of negative cells, not their total. Counting is a useful companion check: a total of zero might mean there are no negative entries, or it might mean negative amounts offset one another in a more complex calculation.

Common reasons the result looks wrong

The amounts are stored as text. Imported values may look like -25 but be text rather than numeric values. SUMIF can then fail to treat them as negative numbers. Check a few cells, and convert the source data to numbers using an appropriate method such as correcting the import, removing unwanted characters, or using Format > Number after confirming the contents are numeric. Formatting alone does not turn every text string into a number.
The minus sign is not a standard minus. Data copied from a PDF or another system may contain a typographic dash or a trailing minus, such as 25-. Those entries can look negative to a person but are not necessarily stored as negative numeric values. Inspect the original cell contents and clean the source if needed.
The wrong range is being checked or summed. If amounts are in column B but the formula checks column A, the result will not reflect the amount values. When using separate criteria and sum ranges, make sure they correspond row by row and span the intended records.
The formula omits a condition or uses the wrong sign. The criterion “>0” selects positive values, whereas “<0” selects negative ones. A total that is unexpectedly positive may also be the net of multiple conditions in a different formula, so inspect the formula and the underlying entries rather than assuming a display issue.
Rows are filtered or hidden. SUMIF normally calculates from the cells in its specified range whether or not those rows are visible after filtering. If the goal is to total only visible rows, SUMIF alone does not express that requirement; use a method designed for visible records or make a separate filtered data range.

Also check for accidental spaces or inconsistent labels when combining a category criterion with the negative test. For example, a cell containing Travel followed by a space may not match a criterion that expects Travel. Cleaning category text or using consistent data validation can prevent category totals from silently omitting records.

Check the total and interpret its sign

For a quick manual check, filter or inspect the source column and add a few qualifying values yourself. In the basic example, the qualifying entries are -12, -5, and -3, so the expected total is -20. If the result differs, confirm the cell range, whether each value is truly numeric, and whether the criterion is exactly less than zero.

A negative SUMIF result is not a positive expense total with a minus sign removed; it is a signed sum. This distinction matters in reports. If a business records expenses as negative values, -165 may be the appropriate accounting-style total. If a reader wants to display the size of that total as a positive number, that is a separate presentation choice. Do not change the sign unless the report’s conventions call for it, because the sign may distinguish charges from credits or outflows from inflows.

For summaries, consider labeling the result clearly, such as “Negative adjustments” or “Net negative amount,” so readers understand what the sign means. A total by itself does not explain why the entries are negative, whether they are expenses, refunds, or corrections, or whether the underlying records are complete. The formula answers a defined calculation question; interpretation depends on the data and its context.

SUMIF, SUMIFS, and alternatives

Use SUMIF when one condition is enough—for example, add every negative value in a single amount column. Use SUMIFS when the total must meet multiple conditions, such as a department, date range, and negative sign. The names can be confusing: SUMIFS places the sum range first, while SUMIF places its condition range and criterion first. Keeping that difference in mind helps avoid accidentally adding the wrong column.

For a single negative-only total, SUMIF is usually simpler than building a longer formula. If you need only to see which rows qualify, filtering the amount column to values less than zero can be more useful than calculating a total. In Google Sheets, use Data > Create a filter, then open the filter menu for the amount column and choose a condition that selects values less than zero. A filter changes which rows are displayed, whereas SUMIF returns a calculated total and leaves the rows visible as before.

Another option is to create a separate report using a query or a filtered range, but that is only necessary when you need a list of matching records or a more elaborate summary. For a basic signed total, SUMIF is direct and easy to audit. If you are moving the sheet to Excel, SUMIF and SUMIFS are also available there, though locale-specific separators and some interface details may differ.

Frequently Asked Questions

How do I sum only negative numbers in Google Sheets?
With numbers in A2:A100, enter =SUMIF(A2:A100,"<0"). It adds cells below zero and excludes zero, positive numbers, and blanks.

Does SUMIF include zero when I use “<0”?
No. Zero is equal to zero, not less than zero. Use “<=0” only if you specifically want to include zero along with negative values.

How do I sum negative values for a particular name or category?
Use SUMIFS with the amount range first, then a category condition and a negative-value condition. For instance, the category-and-amount example above totals only negative Travel entries.

Why does SUMIF return 0 when my cells show negative amounts?
The values may be text, the formula may point at the wrong cells, or the negative sign may be a nonstandard character. Check whether a source cell is numeric and confirm that the criterion range contains the displayed amounts.

How can I count negative values instead of adding them?
Use COUNTIF with the same comparison criterion. For values in A2:A100, =COUNTIF(A2:A100,"<0") counts cells below zero rather than summing their values.

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.