Software

Sum only negative values in a given range using SUMIF()

Summing only the positive or negative values in the same range of values only sounds difficult. Fortunately, the SUMIF() function can handle both.

Summing only the negative values in a range of values sounds like a difficult task, but there's a simple solution-use SUMIF(). In fact, you can use this function to sum all the negative or positive values in the same range.

Sum

First, let's take a brief look at SUMIF(). This function adds values that meet a specific criteria or condition using the syntax:

SUMIF(range, criteria, [sumrange])

The range argument specifies the cells that must the condition expressed by criteria, and sumrange identifies the cells Excel sums when the values in range meet the condition specified by criteria.

When summing only the negative or positive values in the same range, you don't need the optional sumrange argument. Simply use one of the following simpler forms:

SUMIF(range, "<0")

SUMIF(range, ">0")

The simple criteria expression <0 specifies values that are less than 0 (negative); >0 specifies values that are greater than 0 (positive). Since the value 0 won't impact either equation, it's okay to exclude it, but be careful with these types of conditional expressions. Sometimes you'll want to include 0 by using the =< and => operators.

Now, let's use both forms to sum the negative and positive values in column C of the sheet shown below. The values in column C represent units sold or returned. As you can see, the net total is 381 - the result of the simple formula: =SUM(C2:C13). In a case like this, it might be beneficial to track the units sold separately from those returned.

To do so, we'll use the SUMIF() function to determine the total number of units sold or returned, versus the net sales. To sum the total number of units sold, enter the following functions into cells E2 and F2, respectively:

=SUMIF(C2:C13,">0")

=SUMIF(C2:C13,"<0")

The simple formula in G2, =E2+F2, should equal the net total in C14 - and it does.

You can break down the values even further, returning the total units sold, returns, and net units sold for each person by using SUMIFS(). To learn more about this function, read Use SUMIFS() to sum by multiple conditions in Excel. The downloadable demo file includes the SUMIFS() functions. (This function is new to Excel 2007, so it isn't in the xls downloadable).

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
wehmeier
wehmeier

I tried this using a nested and statement and it worked. For example =sumif(Range, and("criteria 1","criteria 2"))

Pillalamarri
Pillalamarri

such as sum only the values greater than "a", and less than "b".

Editor's Picks