SUMIF Negative Values in Google Sheets: What to Check

Negative numbers do not make Google Sheets’ SUMIF function fail: to add every value below zero, use a criterion of "<0". If the formula returns an error or an unexpected total, check the formula syntax, the ranges, and whether the entries are real numbers rather than text.

What SUMIF does with negative numbers

SUMIF tests cells against a condition and adds the corresponding values. Its syntax is SUMIF(range, criterion, [sum_range]). The first range is where Sheets checks the condition; the criterion defines what qualifies; and the optional sum range contains the numbers to add. If you omit the sum range, Sheets adds qualifying cells in the first range itself.

A negative result is not an error. It simply means the qualifying values add to less than zero. For example, adding -12 and -5 gives -17. A negative total can be correct even if you expected a positive number, so first decide whether you want the signed net total or the magnitude of the negative values.

Assuming B2:B7 contains -12, 8, -5, 0, -3, and 20, this formula adds only values below zero: =SUMIF(B2:B7,"<0",B2:B7). It returns -20 because -12 + -5 + -3 = -20. The comparison is strict: zero is not less than zero, so the zero cell is excluded.

How to write the negative-value criterion

For a comparison criterion, put the operator and number together inside quotation marks. In a formula written with the normal English-language separators, "<0" means less than zero, "<=-10" means less than or equal to -10, and ">0" means greater than zero. The quotes are part of how the criterion is supplied; they do not turn the comparison into a search for text in the cells.

Assuming B2:B7 contains the same values as above and D1 contains 0, this formula uses the value in D1 as its cutoff: =SUMIF(B2:B7,"<"&D1,B2:B7). It returns -20 because the cells below zero are -12, -5, and -3. Joining the operator to the cell reference with & is useful when the cutoff changes. If D1 is changed to -5, the formula instead includes values strictly below -5.

If you want the size of the negative total shown as a positive number, you can reverse its sign. Assuming B2:B7 still contains -12, 8, -5, 0, -3, and 20, =-SUMIF(B2:B7,"<0",B2:B7) returns 20. That is a presentation choice, not a different selection rule. Keep the negative sign if the result represents a balance, expense, or change where direction matters.

Check whether you need SUMIF or SUMIFS

A frequent source of a seemingly wrong answer is using a criterion for only one condition when the task requires two. For instance, SUMIF can sum every negative value in a column, but it cannot by itself require both that an entry belongs to a particular category and that its amount is negative. Google Sheets has SUMIFS for multiple conditions.

Assuming A2:A6 contains Travel, Food, Travel, Food, Travel and B2:B6 contains -12, -8, 20, -3, and -5, use =SUMIFS(B2:B6,A2:A6,"Travel",B2:B6,"<0") to sum negative Travel amounts. The result is -17, from -12 + -5; the positive Travel amount of 20 and the Food entries are not included. In SUMIFS, the sum range comes first, followed by each criteria range and criterion pair.

When you only need all negative numbers in one range, SUMIF is simpler. If you need category, date, status, or other filters as well, use SUMIFS and add a pair for each additional condition.

Diagnose an error or an unexpected total

First read the error displayed in the cell. The minus sign is not the explanation by itself. A formula parse error points to how the formula was entered, while an incorrect numeric result often means the condition or source data does not match what you intended. Work through these checks:

Check punctuation and separators. In many Google Sheets locales, formula arguments are separated by commas, as in =SUMIF(B2:B7,"<0",B2:B7). Some spreadsheet locales use semicolons instead. Use the separator expected by the spreadsheet’s locale if Sheets reports a parse error.
Check the quotes and comparison operator. The criterion should be entered as "<0", with straight quotation marks. Curly “smart quotes” copied from formatted text can stop a formula from parsing.
Check the cell references. Confirm that the criterion range contains the amounts you mean to test and that the sum range contains the amounts you mean to add. If the two ranges start on different rows, matching records may no longer line up as intended.
Check for an existing error in the data. If a cell that the calculation needs contains an error such as #N/A or #VALUE!, address the source error rather than assuming a negative number caused it. Inspect the relevant input cells and any formulas feeding them.
Check for text stored as a value. A cell can look like -12 while containing text, especially after importing or pasting data. Select it and inspect the formula bar, then test or convert the source data if necessary.

To inspect the display format, select the cells and choose Format > Number in Google Sheets. Number formats can show negatives with a minus sign or parentheses, and custom formats can affect how values look. Formatting changes the display, not the underlying value. If a cell shows parentheses such as (12), it may still be a numeric -12; verify the actual value before changing the formula.

Numbers stored as text and imported data

Text that resembles a number is a common reason that a negative-value calculation misses entries. A minus sign may be a different character from the standard hyphen-minus, there may be leading or trailing spaces, or an import may have put an apostrophe before the value. A cell’s alignment can offer a clue, but it is not definitive because formatting can change alignment. Check a few representative cells and the original source.

For a quick check, create a temporary helper formula beside an imported value. Assuming B2 contains the suspected entry, =ISNUMBER(B2) returns TRUE if Sheets recognizes it as a number and FALSE if it is text or another nonnumeric value. Remove the temporary check after diagnosing the data. For a whole column, you can also select the cells and use Format > Number > Number, but formatting alone does not reliably convert text into numeric values.

If the values came from a file, the best fix is often to correct the import or clean the source column consistently. Avoid converting blindly if the column contains labels or mixed formats. For example, a value like -12.50 may be interpreted differently if the source uses a decimal comma. Confirm the spreadsheet locale and the source’s number conventions before replacing characters or changing separators.

Make sure the result matches the question

“Sum the negative numbers” can mean more than one thing. The formula with "<0" returns their signed total, which remains negative. If you want the absolute size of that total, negate the result. If instead you want the sum of the absolute value of each qualifying entry, that is a different calculation and should not be confused with merely formatting the final answer as positive.

Also distinguish “negative entries” from “a negative net total.” For example, a list can contain negative and positive amounts whose overall sum is negative. Filtering with "<0" excludes the positive entries; summing the entire range does not. Choose the condition based on whether the question is about individual negative transactions or the overall balance.

When validating a result, compare a small sample manually: list the qualifying cells, confirm that zero and positive values are excluded, and add the selected amounts. This is especially useful when the data includes refunds, reversals, or blank rows, where a plausible-looking total may still answer the wrong question.

Use a criterion cell and keep ranges aligned

A criterion cell makes a report easier to adjust without editing the formula. For example, a cell can hold a threshold such as 0 or -10, while the formula joins that value to a comparison operator. Keep the condition and the sum range pointed at the intended columns, and use the same row span when those columns represent the same records. This reduces accidental shifts when copying formulas to another part of a sheet.

If you fill a formula down a table, consider whether its references should move. A reference such as B2:B100 changes when copied unless locked with dollar signs, while $B$2:$B$100 remains fixed. Use fixed references only when that is the desired behavior; otherwise, relative references can be useful when each row has a separate calculation. Check the copied formula in a couple of rows rather than assuming it still points to the correct data.

Frequently Asked Questions

Does SUMIF work with negative numbers in Google Sheets?
Yes. Use a comparison criterion such as "<0" to add values below zero. A negative result is a valid total, not an error.

Why does SUMIF return 0 when my cells show negative amounts?
The entries may be stored as text, the criterion range may point to the wrong cells, or the values may not actually be below zero. Check the underlying cell value and test whether Sheets recognizes it as a number.

How do I sum only values less than -10?
Use the criterion "<-10" to exclude -10 itself, or "<=-10" to include it. Confirm whether your intended cutoff is strict or inclusive.

Why is the answer negative instead of positive?
SUMIF preserves the signs of the values it adds. If the matching numbers are negative, their sum will be negative. Negate the result only if your report specifically needs the magnitude displayed positively.

Should I use SUMIF or SUMIFS for negative values?
Use SUMIF for one condition, such as all amounts below zero. Use SUMIFS when you also need conditions such as a category or date range.

Does the formula differ in Excel?
The basic SUMIF syntax and negative criterion are also available in Excel, though menu locations and some locale-specific separators can differ. The examples here are written for Google Sheets in a web browser.

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.