Part II: Working with Formulas and Functions
FIGURE 13.10
SUMIF using the TODAY function
There are two wildcard characters that you can use in the condition argument to SUMIF.
The question mark (?) represents any single character, and the asterisk (*) represents zero,
one, or any number of characters. The formula =SUMIF(B2:B11,"?o*",C2:C11) will sum
all the values in C2:C11 that correspond to the values in B2:B11 where the second character
is a lowercase o. If we apply that formula to the data in Figure 13.9, we will get the sum for
sales in both the North and South regions because both have a lowercase o as the second
letter and East does not.
Summing all values that meet two or more conditions
The limitation of SUMIF shown in Figure 13.9 is that it works with only one condition. The
SUMIFS function can be used when more than one condition is needed.
Figure 13.11 shows a partial listing of countries and their gross domestic product (GDP)
from 2000 to 2009. We want to total Brazil’s GDP from 2003 to 2006. Excel’s SUMIFS work-
sheet function is used to sum values where two or more conditions must be met, such as
Country and Year in this example.
=SUMIFS(D3:D212,B3:B212,G3,C3:C212,">="&G4,C3:C212,"<="&G5)
SUMIFS arguments start with the range that contains the value that you want to sum.
The remaining arguments are in pairs that follow the pattern criteria_range, criteria.
Because of the way that the arguments are laid out, SUMIFS will always have an odd
number of arguments. The first criteria pair is required—without at least one condition,
SUMIFS would be no different than SUM. The remaining pairs of conditions, up to 126 of
them, are optional.
=SUMIFS(D3:D212,B3:B212,G3,C3:C212,">="&G4,C3:C212,"<="&G5)
