A conditional test formula compares two numbers, functions, formulas, labels, or logical values. You can use conditional tests to flag values that fall outside a given threshold, for example. You can use simple mathematical and logical operators to construct logical formulas, or you can use an assortment of built-in functions. For information about using conditional test functions, see Understanding Logical Functions on page 550.
Note
You might also be able to satisfy some of your conditional curiosities by using the conditional formatting feature in Excel. For details, see Formatting Conditionally on page 309.
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.
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.
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 2010, but there is now 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.
Note
For details on these functions, see The SUMIF, SUMIFS, and COUNTIF Functions on page 538, and Understanding Logical Functions on page 550.
The SUMIF function allows you to collect a total of items in a range that match a given criterion. In Figure 12-39, the salesperson’s name is used as the criterion to collect sales totals (of course, correct spelling and punctuation is key). If you want to be even more selective, you can use the SUMIFS function, which allows you to specify up to 127 separate sets of criteria. Figure 12-40 shows a SUMIFS function in the formula bar that uses two criteria—salesperson and location. For each criterion, there are two arguments in the formula: one defines the range of cells containing the values from which you want to gather totals, and the other specifies the criterion identifying values that you want to include.
As you might imagine, the COUNTIF and COUNTIFS functions operate similarly to their SUM-family brethren, but instead of adding values together, they count “instances.” For example, Figure 12-41 shows a worksheet displaying the number of times each salesperson worked at each store location. The result cells in the “Worked at Location” area each contain a COUNTIFS function that looks at both the salesperson and the location and returns not a total but a tally of the number of times this combination occurs.
You can create formulas using the INDEX and MATCH functions that pinpoint specific values in tables of data. Typically, a lookup formula includes two MATCH functions, one to identify a row, and one to indentify a column. The INDEX function takes these values and returns the contents of the cell at the specified location.
Versions of Excel prior to the 2010 release included the Lookup Wizard, an add-in that would help construct the necessary formulas using the INDEX and MATCH functions. These formulas still work perfectly, in case you still have some of these wizard-generated models in your workbooks.
Note
For details about the INDEX and MATCH functions, see Understanding Lookup and Reference Functions on page 555.
As you can see in the formula bar in Figure 12-42, there are two MATCH functions at work within the INDEX function; the first locates the row, the second locates the column. They do so using the values in cells P5 and Q5, respectively, containing column and row criteria that you specify. The INDEX function then takes these criteria and returns the value found at the intersection of the designated row and column.
Note
In the previous three figures, you’ll notice a mixture of relative and absolute references in the formulas. (A row number or column letter preceded by a dollar sign is an absolute reference.) These are generally used to allow the copying of formulas to other locations while maintaining the proper relationships. For details, see Understanding Relative, Absolute, and Mixed References on page 469.