Part II: Working with Formulas and Functions
the criteria rather than sum them. Another difference is that there is no optional third
argument as there is in SUMIF. With SUMIF, you can sum a range that’s different from
the range to which the criterion is applied. But with COUNTIF, that wouldn’t make sense
because counting a different range would get the same result.
The formula in this example uses a slightly different technique to construct the criteria
argument. The string concatenation occurs all in cell G3 rather than in the function’s sec-
ond argument. If we had done it the same as SUMIF in Figure 13.11, the second argument
would look like ">=1000000" or ">="&G3 rather than just pointing to G3. You may also
note that the formula in G3, =">="&10^6, uses the exponent operator, or caret (^), to cal-
culate 1 million. Representing large numbers using the caret can help reduce errors caused
by miscounting the number of zeros that you typed.
Getting a count of values that meet two or more conditions
The SUMIF function has its COUNTIF cousin. Of course, Microsoft couldn’t introduce
SUMIFS for summing multiple conditions without also introducing COUNTIFS to count
them.
In Figure 13.14, there is a list of Alpine Skiing medalists from the 1972 Winter Olympics.
We would like to know how many silver medalists have an ö in their name. The letter we’re
looking for is typed in cell I3 and the type of medal in cell I4.
=COUNTIFS(C3:C20,"*"&I3&"*",F3:F20,I4)
FIGURE 13.14
1972 Alpine Skiing Olympic medalists
