Microsoft Office Tutorials and References

In Depth Information

**Using conditional functions**

Each of the following formulas performs a rudimentary conditional test:

=A1>A2

=5–3<5*2

=AVERAGE(B1:B6)=SUM(6,7,8)

=C2="Female"

=COUNT(A1:A10)=COUNT(B1:B10)

=LEN(A1)=10

Every conditional test must include at least one logical operator, which defines the

relationship between elements of the conditional test. For example, in the conditional test A1>A2,

the greater than (>) logical operator compares the values in cells A1 and A2. Table 12-4 lists

the six logical operators.

TABLE 12-4
Logical operators

Operator

Definition

=

Equal to

>

Greater than

<

Less than

> =

Greater than or equal to

< =

Less than or equal to

< >

Not equal to

The result of a conditional test is either the logical value TRUE (1) or the logical value FALSE

(0). For example, the conditional test =A1=10 returns TRUE if the value in A1 equals
10
or

FALSE if A1 contains any other value.

Using conditional functions

Often, you need to total some, but not all, numbers in a range or count items that conform

to specific criteria. You can easily construct these kinds of conditional formulas by using the

SUMIF, SUMIFS, COUNTIF, and COUNTIFS functions.

Versions of Excel prior to the 2010 release included the Conditional Sum Wizard, an add-in

that helped construct the necessary array formulas using the SUM and IF functions. The

formula visible in the formula bar of the background worksheet in Figure 12-39 shows just

such a legacy formula that was originally created by the wizard. This approach, and this

formula, still works just fine, in case you still have some of these wizard-generated models

in your workbooks. The Conditional Sum Wizard is history in 2013, but there is a somewhat

easier method of arriving at the same result. The worksheet in the foreground of Figure

12-39 shows a similar task accomplished with SUMIF functions.

For details on these functions, see “The SUMIF, SUMIFS, and COUNTIF functions” and “Under-

standing logical functions,” both of which are in Chapter 14.