Formulas and Functions

Chapter 13 Additional Examples and Topics 361
Function Allows numeric comparisons Accepts wildcards
HLOOKUP no if exact match specied
MATCH no if exact match specied
VLOOKUP no if exact match specied
Examples of conditions, both with and without wildcards, are illustrated in this section.
Expression Example
“>4” means match any number greater than 4. =COUNTIF(B2:E7, “>4”) returns a count of the
number of cells in the range B2:E7 that contain a
value greater than 4.
“>=7” means match any number greater than or
equal to 7.
=SUMIF(B, “>=7”) sums the cells in the column B
that contain a value greater than or equal to 7.
“<=5” in combination with “>=15” means match
any number less than or equal to 5 or greater
than or equal to 15. Numbers 6 through 14,
inclusive, would not be included.
=SUMIF(A3:B12,”<=5”)+SUMIF(A3:B12,”>=15”)
sums the cells in the range A3:B12 that contain
a value less than or equal to 5 or greater than or
equal to 15.
“*it” means any value that ends with “it.” The
asterisk (*) matches any number of characters.
=COUNTIF(B2:E7, “*it”) returns a count of the
number of cells in the range B2:E7 that contain a
value that ends with “it” such as “bit” and “mit.” It
does not match “mitt.”
“~*” means to match the asterisk (*). The tilde
(~) character means to take the next character
literally, instead of treating it as a wildcard.
=COUNTIF(E, “~*”) returns a count of the number
of cells in column E that contain the asterisk
character.
B2 & “, “ & E2 returns the contents of cells B2 and
E2 separated by a comma and a space.
=B2&”, “&E2 returns “Last, First” if B2 contains “Last”
and E2 contains “First.”
“?ip” means any value that begins with a single
character followed by “ip.”
=COUNTIF(B2:E7, “?ip”) returns a count of the
number of cells in the range B2:E7 that contain
a value that starts with a character followed by
“ip” such as “rip” and “tip.” It does not match “drip”
or “trip.”
“~?” means to match the question mark (?).
The tilde (~) character means to take the next
character literally, instead of treating it as a
wildcard.
=SEARCH(“~?”, B2) returns 19 if cell B2 contains
“This is a question? Yes it is.”, since the question
mark is the 19th character in the string.
“*on?” means to match any value that begins
with any number of characters followed by “on”
and then a single character.
=COUNTIF(B2:E7, “*on?”) returns a count of the
number of cells in the range B2:E7 that contain a
value that starts with any number of characters
(including none) followed by “on” and then a
single character. This matches words such as
“alone”, “bone”, “one”, and “none.” This does not
match “only” (has two characters after the “on”) or
“eon” (has no characters after the “on”).