SUMIF Negative Numbers in Google Sheets: Syntax & Examples

To sum values that are negative in Google Sheets, use =SUMIF(A2:A100,"<0",B2:B100). This example checks A2:A100 for numbers below zero and adds the matching values from B2:B100; if the numbers you want to total are in column A itself, use that range for both the criteria and sum ranges.

SUMIF syntax for negative values

SUMIF adds values when a corresponding cell meets a condition. Its argument order is the range to test, the criterion, and optionally the range to add. The criterion for a negative number is the text string "<0": the less-than sign means “below,” and zero is the boundary.

Assuming the values to test and total are both in A2:A100, the formula is =SUMIF(A2:A100,"<0",A2:A100). This sums only negative numeric entries. If you omit the third argument, Google Sheets sums the cells in the first range that meet the criterion. Including the third argument explicitly can make a formula easier to understand, especially when the test values and amounts to total are in different columns.

The quotation marks around <0 matter because the criterion is written as a comparison expression. Do not put the whole formula in quotes, and do not write the criterion as an unquoted comparison. Google Sheets uses commas between arguments in the standard US-style spreadsheet locale. In some locales, formulas use semicolons instead; the function and criterion stay the same, but the separator can differ.

Worked example: total the negative amounts

Suppose A2:A6 contains transaction amounts: -25, -12, 8, 0, and -3. To add the negative entries in that same range, use =SUMIF(A2:A6,"<0",A2:A6). The selected values are -25, -12, and -3, so the result is -40. The positive 8 and zero are not included.

To keep a total of expenses positive instead, you can negate the result. Assuming the same amounts are in A2:A6, use =-SUMIF(A2:A6,"<0",A2:A6). The result is 40. This changes the sign of the final total; it does not change which entries match. Whether a negative or positive expense total is more useful depends on how the rest of the sheet represents expenses.

For a more common two-column layout, let A2:A6 hold transaction types or status labels and B2:B6 hold amounts. To add amounts only where the amount itself is negative, the condition must be tested against the amount column. With the amounts in B2:B6, use =SUMIF(B2:B6,"<0",B2:B6). A separate label column is not needed for this particular condition.

Test one column and sum another

The first argument is always the range that determines whether a row qualifies; the optional third argument is the range whose values are added. For example, say A2:A5 contains account balances and B2:B5 contains each account’s adjustment. To total adjustments for rows where the balance is negative, use =SUMIF(A2:A5,"<0",B2:B5). The formula checks column A but adds corresponding entries from column B.

Keep the criteria range and sum range aligned row by row. They should cover the same number of rows, and the first row in each range should refer to the same record. If A2:A20 is paired with B3:B21 by mistake, the formula can add an adjustment from the wrong row. A SUMIF formula does not match records by a separate ID; it pairs cells by position.

If you leave out the sum range, SUMIF uses the criteria range as the values to add. That is convenient when testing and totaling one column, but it will not total a second column just because it sits next to the first. Check the argument order if the result seems to count or sum the wrong information.

Useful variations on the negative criterion

The strict comparison "<0" excludes zero. If you mean zero or any negative number, use a less-than-or-equal criterion instead. Assuming the values are in A2:A20, the formula is =SUMIF(A2:A20,"<=0",A2:A20). This will include zero, which does not change the arithmetic total but may matter when you are matching rows in a separate sum range.

To sum values below a different threshold, replace zero with that number. For instance, if the amounts are in B2:B30 and the goal is to add amounts below -10, use =SUMIF(B2:B30,"<-10",B2:B30). This excludes -10 itself. For values at or below -10, use "<=-10" as the criterion.

You can combine the negative-number condition with a text condition using SUMIFS, which supports multiple criteria. Suppose A2:A20 contains category names and B2:B20 contains amounts. To sum negative amounts in the “Travel” category, use =SUMIFS(B2:B20,B2:B20,"<0",A2:A20,"Travel"). Here SUMIFS takes the sum range first, followed by each criteria range and criterion pair. That order differs from SUMIF, so check it when switching functions.

Check that the values are numbers

A cell can look like a negative number without being stored as one. Imported data may contain text such as a minus sign followed by digits, or a formatted-looking value with extra spaces. SUMIF’s numeric comparison is intended to match numeric values; text entries that merely resemble numbers may be skipped. If some visible negatives are missing from the total, inspect a few source cells and confirm they behave as numbers in calculations.

Number formatting is different from the underlying value. A numeric value might display in parentheses, such as (25), through a custom number format while its stored value is -25. Such a value still qualifies for the negative criterion. Conversely, changing the cell’s appearance does not convert text into a true number. In Google Sheets, changing the format through Format > Number can change display, but it is not a reliable fix for text data that needs conversion.

Also watch for values imported with a currency symbol, leading apostrophe, nonstandard minus character, or spaces. A quick check is to select a suspected cell and look at the formula bar, then try a simple arithmetic operation on a copy of the data. For a large import, clean or convert the source column before relying on totals. Avoid changing the original records until you have checked how the data was generated and retained a safe copy.

Common SUMIF errors and how to fix them

Missing quotes around the comparison: enter the criterion as "<0", including its quotation marks.
Using the wrong range: the first range must contain the negative values you want to test. If amounts are in column C, testing column A will not identify negative amounts in C.
Misaligned ranges: ensure the criteria and sum ranges begin and end on matching rows.
Including zero unintentionally: "<=0" includes zero; use "<0" to exclude it.
Numbers stored as text: convert or clean the source data rather than assuming a number format will fix it.
Locale separator mismatch: if Sheets reports a formula parse error, your spreadsheet locale may expect semicolons instead of commas.

For a formula parse error, inspect quotation marks and parentheses first, then check the argument separator used by your file. If the formula returns zero unexpectedly, verify that the range actually contains numeric negatives and that the formula points to the intended column. If it returns a total that is too large, check whether the criteria accidentally include zero or whether duplicate rows exist in the source data.

SUMIF, SUMIFS, and other options

Use SUMIF when one condition is enough, such as “add every number below zero.” It is concise and works well for a single test. Use SUMIFS when the total must meet multiple conditions—for example, negative amount plus a particular department or date period. Remember that SUMIFS puts the sum range first, while SUMIF puts its criteria range first.

If you only need to know how many negative entries exist, use COUNTIF rather than summing them. Assuming the numbers are in A2:A100, enter =COUNTIF(A2:A100,"<0"). This returns a count, not the value total. If you need both, place the two formulas in separate cells and label each result so readers do not confuse a count of transactions with a sum of amounts.

For a simple total of negative values, SUMIF is usually the clearest option. A filter or pivot table can be useful when you want to inspect or summarize many categories interactively, but those tools are not necessary for one criterion and one total. Google Sheets and Excel both support SUMIF and SUMIFS with broadly similar syntax; menu locations and some locale behavior can differ, but the core negative criterion is the same.

Frequently Asked Questions

What is the SUMIF formula for negative numbers in Google Sheets?
Use =SUMIF(range,"<0",sum_range) with the range containing the values to test and, if needed, a sum range containing corresponding amounts. If you are testing and summing the same cells, use that range for both.

Does SUMIF "<0" include zero?
No. The less-than condition matches numbers strictly below zero. Use "<=0" if zero should also qualify.

Why is SUMIF returning zero when I can see negative amounts?
The cells may contain text rather than numeric values, or the formula may be checking a different range. Verify the data type, the first argument, and the exact rows included in the formula.

Can SUMIF add negative values from a different column?
Yes. Put the column to test first and the column to add third. The two ranges must correspond to the same rows; for multiple conditions, use SUMIFS.

How do I show the negative total as a positive number?
Wrap the SUMIF result in a leading minus sign, such as =-SUMIF(A2:A20,"<0",A2:A20). This reverses the sign of the total while still selecting only 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.