How to Use Excel Countif

By Regina Edwards

The COUNTIF function in Excel analyzes a range of cells and counts one point for each cell that matches a criteria. Criteria can be specified as text, numbers, or a data value in another cell. As with most formulas in Excel, functions may be combined in a formula format for an advanced set of calculations.

Step 1

Select the desired cell on the spreadsheet to display the calculated formula.

Step 2

Type "=countif(" and select the range of cells to be evaluated by clicking the first cell, holding down the mouse button and dragging the cursor to the last cell before releasing the mouse button.

Step 3

Type "," and then identify the criteria for counting each cell's content as "TRUE" for one point, making sure to enclose characters between quotation marks. For example, type "purple" within quotation marks to count those cells containing the word; type ">9" within quotation marks to count the number of cells containing a number above nine; type A2 to count those cells containing information matching the contents in cell A2.

Step 4

Type ")" and then press the "Enter" key to complete the formula.

Tips & Warnings

  • Consider appending functions and formulas to the COUNTIF formula in the cell, if applicable. For example, to calculate the percentage of "Yes" responses out of 100 total responders, type ")/100" for step 4 before pressing the "Enter" key. The formula will read "=countif(A2:A101,"Yes")/100" in the cell.
  • Use "?" or "*" to specify a single character or string of characters (respectively) for matching criteria. For example, "=countif(A2:A101,"?old")" will count cells containing the four-letter words ending in "-old" such as "hold," "bold" and "cold." The formula "=countif(A2:A101,"*old")" will count all cells with words ending in "-old" (no matter how many letters they include) such as "old," "fold," "behold" and "trifold."