SUMIF Negative in Google Sheets? Alternatives That Work

Google Sheets can sum negative numbers directly with SUMIF: use the criterion "<0", as in =SUMIF(B2:B7,"<0",B2:B7). You usually do not need an alternative; use SUMIFS when you need additional conditions, or consider FILTER or SUMPRODUCT for different kinds of calculations.

The short answer: SUMIF already handles negatives

SUMIF is not limited to positive values. It checks each cell against a criterion, then adds the corresponding value from the sum range. A negative-number test is written as a text criterion in quotation marks: "<0". The less-than sign means “below,” and zero is the dividing point between negative and nonnegative numbers.

For a simple list where the numbers to check are also the numbers to add, the range can serve as both the test range and the sum range. With amounts in B2:B7, =SUMIF(B2:B7,"<0",B2:B7) adds only negative entries. The shorter form, =SUMIF(B2:B7,"<0"), does the same thing because SUMIF uses its first range as the sum range when the optional third argument is omitted.

If a formula returns zero when you expected a negative total, the issue is more likely to be the data, range, or criterion than a limitation of SUMIF. The sections below show how to check those points and when another function is genuinely useful.

How the negative-number criterion works

The general structure is SUMIF(range, criterion, [sum_range]). The first range is where Google Sheets looks for matches. The criterion tells it what to match, and the optional sum range contains the numbers to add. The two ranges should cover corresponding rows so each match is paired with the intended amount.

Suppose column A contains categories and column B contains transaction amounts. Here is a small example:

Travel: -12
Food: 8
Travel: -5
Travel: 0
Travel: -3
Food: -4

With the amounts in B2:B7, the formula =SUMIF(B2:B7,"<0",B2:B7) returns -24: -12 + -5 + -3 + -4. It excludes 8 because it is positive and 0 because zero is not less than zero. This is a useful distinction: a zero value is neither negative nor positive for this test.

To add negative amounts only for Travel, the formula needs to test two things: the category and the amount. With categories in A2:A7 and amounts in B2:B7, =SUMIFS(B2:B7,A2:A7,"Travel",B2:B7,"<0") returns -20, from -12, -5, and -3. SUMIF itself accepts one criterion; SUMIFS is the natural next step when more than one condition is required.

When SUMIFS is a better fit

Choose SUMIFS when the negative amount must also meet another condition, such as belonging to one department, falling within a date period, or matching a selected account. Its argument order is worth noting: SUMIFS starts with the sum range, followed by pairs of criteria ranges and criteria. That differs from SUMIF, which starts with the range to test.

For example, the Travel formula above first sums B2:B7, then checks A2:A7 for Travel and B2:B7 for values below zero. Additional conditions can be added as further range-and-criterion pairs. Make sure each criteria range has the same dimensions as the sum range; mismatched ranges can cause errors or make the calculation harder to audit.

If your condition changes often, put the criterion in a cell rather than typing it into each formula. With amounts in B2:B7 and the text <0 entered in D2, =SUMIF(B2:B7,D2,B2:B7) uses the value in D2 as its condition. This is handy for a small report where someone may switch between criteria, but label the criterion cell clearly so a reader knows what it controls.

Alternatives: FILTER and SUMPRODUCT

An alternative is useful when you want to inspect the matching records, combine filtering with other calculations, or express a condition in a way that is convenient for a larger formula. It does not automatically mean the alternative is more accurate or faster. For the straightforward task of totaling negative values, SUMIF is usually the clearest starting point.

FILTER returns the values that meet a condition, and SUM adds those returned values. With amounts in B2:B7, =IFNA(SUM(FILTER(B2:B7,B2:B7<0)),0) returns -24 for the example. The IFNA wrapper makes the result zero if there are no negative values, because FILTER otherwise reports that it found no matches. Use this pattern when you may also want to display or further examine the filtered rows; if all you need is a total, SUMIF is shorter.

SUMPRODUCT can total values after applying a condition in an array calculation. With amounts in B2:B7, =SUMPRODUCT((B2:B7<0)*B2:B7) also returns -24. The comparison produces a true-or-false test for each value, and multiplication makes the nonmatching entries contribute zero. This can be useful when combining array conditions in one expression, though the logic may be less obvious to a spreadsheet reader than SUMIF or SUMIFS.

Google Sheets supports all three approaches. Excel also has SUMIF, SUMIFS, SUMPRODUCT, FILTER, and IFNA, although availability of newer functions such as FILTER depends on the Excel version. The formulas shown here are for Google Sheets in a web browser; the core SUMIF and SUMIFS patterns are also familiar in current Excel versions.

Choosing the right method

Use the simplest function that expresses the task clearly. A workbook often gets maintained by someone other than its original author, so transparent formulas are a practical advantage.

Use SUMIF to total negative values in one range, or to sum amounts matching one condition.
Use SUMIFS when the total must satisfy two or more criteria, such as category and negative amount.
Use FILTER with SUM when you may also need the matching values or rows, and handle the no-match case if zero is the desired result.
Use SUMPRODUCT when array-style conditions suit a more involved calculation, and document the logic if it is not immediately clear.

Think about the output you want, not just which function is shortest. If colleagues need to see which transactions made up a total, a filtered list next to the total can be more useful than a single number. If the sheet is a compact summary and the criteria are fixed, SUMIF or SUMIFS is generally easier to read.

Common problems and how to troubleshoot them

The formula returns zero. Confirm that the values are real numbers and that the formula checks the cells you intended. Imported amounts can look numeric but be stored as text, especially when they contain currency symbols, spaces, or separators that do not match the sheet’s locale. Try selecting a sample cell and checking whether the value behaves like a number in other calculations; clean or convert imported data rather than relying on a criterion to repair it.

The minus sign is in the wrong place. A negative number is stored as a value such as -12. The criterion is a separate expression, written as "<0", not as a comparison typed after the range without quotes. In a cell-based criterion, the cell must contain the criterion text, for example <0, rather than a number formatted to look like a condition.

The result is too high or too low. Check whether the formula includes a header row, omits recent rows, or sums the wrong column. If you use separate criteria and sum ranges, verify that their first and last rows line up. A formula can be syntactically valid while pairing the category from one row with the amount from another if the ranges are offset.

Some negative-looking entries are skipped. A cell may contain text such as a hyphen or a typographic minus instead of a numeric negative sign. Currency-formatted numeric cells are fine; formatting changes how a number appears, not its underlying value. But a pasted string that merely looks like a number may not compare as expected. Standardizing the source data is more dependable than adding increasingly complex formulas.

The total includes values you did not expect. Check whether you asked for less than zero or less than or equal to zero. The criterion "<0" excludes zero; "<=0" includes it. The boundary matters in refunds, adjustments, or reports where zero entries are intentionally retained.

Make the result easier to maintain

Use descriptive headers such as Category and Amount, keep source data in consistent columns, and avoid putting notes or subtotals inside the middle of a data range. These habits make the formula easier to verify and reduce the chance that new rows fall outside its references. For a growing sheet, periodically confirm that the formula’s ranges include newly added records.

It is also useful to check the result against a small sample. Filter or visually inspect the negative transactions, add a few by hand, and compare that subtotal with the formula. This is not a replacement for a correct formula, but it can reveal a text-number problem or a range that accidentally excludes a row. For recurring reports, add a label that states exactly what the total means, such as “Negative Travel amounts,” rather than a generic “Total.”

When sharing the sheet, explain whether the result is a signed total or a positive measure of losses. The formulas above preserve the minus sign, so a total of negative entries appears as a negative number. If a report instead needs the absolute size of those entries, make that reporting choice explicit; do not silently change signs in a way that could confuse readers about the underlying transactions.

Frequently Asked Questions

Can SUMIF add only negative numbers in Google Sheets?
Yes. Set the criterion to "<0" and use the amount range as the sum range. This excludes both positive values and zero.

How do I sum negative values in one category?
Use SUMIFS with the amount range as the sum range, then add a category criterion and a negative-number criterion. That lets the formula require both conditions for each row.

Why does SUMIF return zero for negative amounts?
Check the criterion spelling, the referenced range, and whether the entries are numeric rather than text. Also confirm that the cells contain actual values below zero, not a dash or another visual marker.

Does SUMIF include zero when I use less than zero?
No. "<0" excludes zero. Use a less-than-or-equal condition only if zero should be included in the total.

Should I use SUMIF or SUMPRODUCT?
For one negative-number condition, SUMIF is usually clearer. SUMPRODUCT is an option for array-based logic, while SUMIFS is generally easier to maintain when you are adding ordinary criteria such as category or date.

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.