Page 424 - Excel 2013 All-in-One For Dummies
P. 424
406 Statistical Functions
the table_data range have values greater than 5, you enter this version of the COUNTIF function:
=COUNTIF(table_data,”>5”)
When you want to use the COUNTIF function to find out the number of cells whose contents are equal to the contents of a particular cell in the work- sheet, you just add the cell reference as the function’s criteria argument. For example, if you want to count the number of cells in the table_data range that are equal to the contents of cell B3 in the worksheet, you enter this for- mula:
=COUNTIF(table_data,B3)
However, when you want to specify an expression other than equality that refers to the contents of a cell in the worksheet, you must enclose the opera- tor in a pair of double quotation marks and then add the ampersand (&) concatenation operator before the cell reference. For example, if you want to count how many cells in the table_data range have a value greater than the contents of cell B3, you enter this form of the COUNTIF function:
=COUNTIF(table_data,”>”&B3)
Note that when specifying text as the condition, you can use the two wild- card characters: the asterisk (*) to represent an unspecified amount of characters and the question mark (?) to represent single characters in the COUNTIF function’s criteria argument. For example, to count all the cells in the table_data range whose text entries end with the word Street, you use the asterisk in the COUNTIF criteria argument as follows:
=COUNTIF(table_data,”*Street”)
To count the cells in the table_data range whose text entries contain the word discount anywhere in the entry, you sandwich discount between two asterisks in the COUNTIF criteria argument as follows:
=COUNTIF(table_data,”*discount*”)
To count the cells in the table_data range whose cell entries consist of any two characters followed by the letter y (as in day, say, pay, and so on), you use two question marks to stand in for the nonspecific characters followed by a y in the COUNTIF criteria argument, as in
=COUNTIF(table_data,”??y”)
