Excel 2019 Bible

Chapter 13: Using Formulas for Conditional Analysis

13

TABLE 13.2 A Truth Table for an OR Function Nested in an AND

Function

OR Logical 1 OR Logical 2 OR Result AND Logical 2 Final Result

TRUE TRUE TRUE TRUE TRUE

TRUE FALSE TRUE TRUE TRUE

FALSE TRUE TRUE TRUE TRUE

FALSE FALSE FALSE TRUE FALSE

TRUE TRUE TRUE FALSE FALSE

TRUE FALSE TRUE FALSE FALSE

FALSE TRUE TRUE FALSE FALSE

FALSE FALSE FALSE FALSE FALSE

Performing Conditional Calculations

Simple conditional functions like IF generally work on only one value or cell at a time.
Excel provides some different conditional functions for aggregating data, such as summing
or averaging.

In this section, you’ll dive into some of the techniques for applying calculations based on a
given set of conditions.

Summing all values that meet a certain condition

Figure 13.8 shows a listing of accounts with positive and negative values. We want to sum
all of the negative balances, which we will later compare to the sum of all the positive bal-
ances to ensure that they are equal. Excel provides the SUMIF function to sum values based
on a condition.

=SUMIF(C3:C12,"<0")

SUMIF takes each value in C3:C12 and compares it to the condition (the second argument
in our function). If the value is less than zero, it meets the condition and is included in
the sum. If it is zero or greater, the value is ignored. Text values and blank cells are also
ignored. For the example in Figure 13.8, cell C3 is evaluated first. Because it is greater than
zero, it is ignored. Next, cell C4 is evaluated. It meets our condition of being less than zero,
so it is added to the total. This continues for each cell. When it’s complete, cells C4, C7, C8,
C9, and C11 are included in the sum, and the others are not.

The second argument of SUMIF, the condition to be met, has quotes around it. Because
we’re using a less-than sign for this example, we have to create a string that represents the
expression.