How to Use Excel's CountIf Function

Excel's CountIf function counts the number of cells in a specified range that meet the specified criteria. The syntax is CountIf (range,criteria), where range is the cell range for which you wish to count cells and criteria is the criteria the cell value must meet in order to be counted. The following steps will show how to use Excel's CountIf function.

Instructions

1. Study the arguments for Countif. Range is a cell range of the form cell_1:cell_2 and criteria is a cell reference, expression, number or text that defines the cells to be counted. Acceptable values include 16, "16", "<16", A2 or "apples."

2. Enter special characters in the criteria for wild card matching. An asterisk (*) matches a series of characters and a question mark (?) matches one character. Place a tilde (~) in front of an asterisk or question mark if you wish to specify an actual asterisk or question mark. Excel has additional functions that may be used in a condition.

3. Know when to use alternate functions. Use the Count A and CountBlank functions to count empty or non-empty cells. Use the Sumif function to calculate a sum based on a number within a range or string. Use the If function to return a value based on a formula.

4. Look at some common uses of CountIf. If A2="apples," A3="oranges," A4="peaches" and A5="apples" then CountIf(A2:A5, "apples") = 2.

5. Examine the use of wild cards in CountIf. Using the same values as in Step 4, we have CountIf(A2:A5,"*es") = 4 and CountIf(A2:A5,"?????es") = 2.

Resources

• More About Excel on the Microsoft Website