How to Count Data in Selected Cells with Excel's COUNTIF Function

Businessmen studying graphs on an interactive screen in business meeting
Count and better manage your data with Excel's COUNTIF Function. Monty Rakusen / Getty Images

The COUNTIF function combines the IF function and COUNT function in Excel; this combination allows you to count the number of times specific data is found in a selected group of cells. The IF portion of the function determines what data meets the specified criteria and the COUNT part does the counting.

of 04

Excel COUNTIF Function Syntax

Screenshot of Excel showing the COUNTIF Function in action

In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the COUNTIF function is as follows:

= COUNTIF ( Range, Criteria )

The function's arguments tell the function what condition we are testing for and what range of data to count when the condition is met.

Range — The group of cells the function is to search.

Criteria — The value compared with the data in the Range cells. If a match is found then the cell in the Range is counted. Actual data or the cell reference to the data can be entered for this argument.

of 04

Entering the Example Data

Screenshot of Excel showing example data for article

Following the steps in this article walks you through creating and using the COUNTIF function seen in the image above to count the number of sales representatives with more than 250 orders.

The first step to using the COUNTIF function in Excel is to enter the data. Enter the data into cells C1 to E11 of an Excel worksheet as seen in the image above.

The COUNTIF function and the search criteria (greater than 250 orders) will be added to row 12 below the data.

The tutorial instructions do not include formatting steps for the worksheet; this will not interfere with completing the tutorial. Your worksheet will look different than the example shown, but the COUNTIF function will give you the same results.

of 04

Building the COUNTIF Function

Screenshot of Excel showing the Formula Builder

Although it is possible to type the COUNTIF function into a cell in a worksheet, many people find it easier to use the Formula Builder to enter the function.

If you are using an older version of Excel, the Formula Builder may be replaced by a Function Arguments dialog box; simply insert the data in the same way presented in this article.

  1. Click on cell E12 to make it the active cell — this is where we will enter the COUNTIF function.
  2. Click on the Formulas tab of the ribbon.
  3. Choose More Functions > Statistical from the ribbon.
  4. Click on COUNTIF in the list to bring up the Formula Builder.

The data that we enter into the two blank rows in the dialog box will form the arguments of the COUNTIF function. These arguments tell the function what condition we are testing for and what cells to count when the condition is met.

Range Argument

The Range argument tells the COUNTIF function which group of cells to search when trying to find the specified criteria.

  1. In the Formula Builder, click on the Range line.
  2. Highlight cells E3 to E9 on the worksheet to enter these cell references as the range to be searched by the function.

Criteria Argument

The Criteria argument tells COUNTIF what data it should try to find in the Range argument. Although actual data — such as text or numbers like > 250 can be entered into the dialog box for this argument, it is usually best to enter a cell reference into the dialog box, such as D12 and then enter the data we want to match into that cell in the worksheet.

  1. Click on the Criteria line.
  2. Click on cell D12 to enter that cell reference. The function will search the range selected in the previous step for data that matches whatever data is entered into this cell.
  3. Click Done to close the dialog box and complete the function.

An answer of zero should appear in cell E12 — the cell where we entered the function — because we have not yet added the data to the Criteria field (D12).

of 04

Adding the Search Criteria

Screenshot of Excel showing the final result of the COUNTIF example

The last step in the tutorial is to add the criteria we want the function to match. In this case, we want the number of Sales Reps with more than 250 orders for the year to be counted. To do this we enter > 250 into D12 — the cell identified in the function as containing the criteria argument.

  1. In cell D12 type > 250 and press the Enter key on the keyboard.
  2. The number 4 should appear in cell E12.