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.
Search JabSto ::

Custom Search