Excel 2019 Bible

Chapter 13: Using Formulas for Conditional Analysis

13

Summing greater than zero

Figure 13.8 also shows the total of all the positive balances. The formula for that calcula-
tion is =SUMIF(C3:C12,">0"). Note that the only difference between this formula and
our example formula is the expression string. Instead of "<0" as the second argument, this
formula has ">0".

We don’t have to include zero in our calculation because we’re summing and zero never
changes a sum. If, however, we were interested in summing numbers greater or less than
1,000, we couldn’t simply use "<1000" and ">1000" as our second arguments because we
would never include anything that was exactly 1,000.

When you use greater than or less than a nonzero number in a SUMIF, make either the
greater than a greater than or equal to, such as ">=1000", or the less than a less than
or equal to, such as "<=1000". Don’t use the equal sign for both; just use one. This will
ensure that you include any numbers that are exactly 1,000 in one or the other calculation
but not both.

The syntax needed to use your comparison operators can be tricky. Table 13.3 lists a set of
simple rules that can help you get it right every time.

TABLE 13.3 Simple Rules for Using Comparison Operators

To Set a Condition... ...Follow These Rules For Example

Equal to a number or

cell reference

Don’t use an equal sign or any

double quotes.

=SUMIF(A1:A10,3)

Equal to a string Don’t use an equal sign, but

put the string in quotes.

=SUMIF(A1:A10,"book")

Nonequal comparison

to a number

Put both the operator and the

number in double quotes.

=SUMIF(A1:A10,">=50")

Nonequal comparison

to a string

Put both the operator and the

string in double quotes.

=SUMIF(A1:A10,"<>Payroll")

Nonequal comparison

to a cell reference or

formula

Put the operator in double

quotes and concatenate the

cell reference or formula with

the ampersand (&).

=SUMIF(A1:A10,"<"&C1)

You can use the TODAY function (to get the current date), or most other functions, in the
second argument. Figure 13.10 shows a listing of dates and values. To sum a range of num-
bers that correspond to today, use the formula =SUMIF(B3:B11,TODAY(),C3:C11). To
sum only those values that are today or before, concatenate the less-than-or-equal-to sign
to the function, such as =SUMIF(B3:B11,"<="&TODAY(),C3:C11).