Chapter 13: Using Formulas for Conditional Analysis
13
The criteria_range and criteria arguments come in pairs just like in SUMIFS. Whereas
SUMIFS will always have an odd number of arguments, COUNTIFS will always have an even
number.
The first criteria_range argument is the list of athlete names in C3:C20. The matching
criteria argument, ""&I3&"", surrounds whatever is in I3 with asterisks. Asterisks
are wildcard characters in COUNTIFS that stand for zero, one, or more characters of any
kind. By including an asterisk both before and after the character, we’re asking Excel
to count all of the names that include that character anywhere within the name. That
is, we don’t care if there are zero, one, or more characters before ö, and we don’t care
if there are zero, one, or more characters after ö as long as that character is in there
somewhere.
The second criteria_range, criteria argument pair counts those entries in F3:F20 that are
SILVER (the value typed into I4). Only those rows where both the first argument pair and
the second argument pair match (only rows where the athlete’s name contains ö and the
medal won was silver) are counted. In this example, Gustav Thöni won the silver in the
Men’s Slalom, and Annemarie Moser-Pröll placed in both the Women’s Downhill and the
Women’s Giant Slalom for a count of three.
Finding nonstandard characters
The ö was typed into cell I3 by holding down the Alt key and typing 0246 on the numeric
keyboard. Don’t try to type those numbers on the number keys across the top of your key-
board because it won’t work. The number 246 is the ASCII code that represents ö. Every
character in this chapter has an ASCII code.
In cells H8:I12 in Figure 13.14, you can see a small table of characters and their codes. In
cell H8, the formula =MID($C$8,ROW(),1) returns the eighth character from the name in
cell C8. (The eighth character was chosen somewhat haphazardly. It was somewhere before
the character we’re looking for but not too far away.) That formula is copied down a few
rows until the character we want to inspect shows up. The character we want is in H10. The
dollar signs in $C$8 anchor that cell reference so that it doesn’t change as the formula is
copied. The ROW() function without an argument returns the row of whatever cells it’s in.
As the formula is copied down, ROW() returns 8, 9, 10, and so on.
In cell I8 is the formula =CODE(H8). The CODE worksheet function returns the ASCII
code for the letter that’s passed in. In this example, we can see that a capital T is ASCII
code 84, a lowercase i is ASCII code 105, and ö is ASCII code 246. Armed with that
knowledge, we can hold down the Alt key and type the code to use that character any-
where we want.
Getting the average of all numbers that meet a certain condition
After summing and counting, taking an average of a range of numbers is the next most
common aggregator. The average, also known as the arithmetic mean, is the sum of the
numbers divided by the count of the numbers.