Count Data in Selected Cells with Excel COUNTIF

01
of 07

How the COUNTIF Function Works

Excel COUNTIF Function Tutorial
Excel COUNTIF Function Tutorial. © Ted French

The COUNTIF Function Overview

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.

COUNTIF Function Step by Step Tutorial

This tutorial uses a set of data records and the COUNTIF function to find the number of Sales Reps who have more than 250 orders for the year.

Following the steps in the tutorial topics below walks you through creating and using the COUNTIF function seen in the image above to count the number of sales reps with more than 250 orders.

Tutorial Topics

  • Entering the Tutorial Data
  • The COUNTIF Function's Syntax
  • Starting the COUNTIF Function
  • Entering the Range Argument
  • Entering the Criteria Argument
  • And the Answer is ..

02
of 07

Entering the Tutorial Data

Excel COUNTIF Function Tutorial
Excel COUNTIF Function Tutorial. © Ted French

Entering the Tutorial Data

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.

Note: 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.

03
of 07

The COUNTIF Function's Syntax

The COUNTIF Function's Syntax
The COUNTIF Function's Syntax. © Ted French

The COUNTIF Function's Syntax

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:

= COUNTIF ( Range, Criteria )

The COUNTIF Function's Arguments

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 - this value is 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.

04
of 07

Starting the COUNTIF Function

Opening the COUNTIF Function Dialog Box
Opening the COUNTIF Function Dialog Box. © Ted French

Opening the COUNTIF Function Dialog Box

Although it is possible to just type the COUNTIF function into a cell in a worksheet, many people find it easier to use the function's dialog box to enter the function.

Tutorial Steps

  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 to open the function drop down list.
  4. Click on COUNTIF in the list to bring up the COUNTIF function's dialog box.

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.

05
of 07

Entering the Range Argument

Entering the Excel COUNTIF Function Range Argument
Entering the Excel COUNTIF Function Range Argument. © Ted French

Entering the Range Argument

In this tutorial we want to find the number of Sales Reps who sold more than 250 orders for the year.

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

Tutorial Steps

  1. In the dialog box, 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.
  3. Leave the dialog box open for the next step in the tutorial.

06
of 07

Entering the Criteria Argument

Entering the Excel COUNTIF Function Criteria Argument
Entering the Excel COUNTIF Function Criteria Argument. © Ted French

Entering the 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.

Tutorial Steps

  1. Click on the Criteria line in the dialog box.
  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 OK to close the dialog box and complete the COUNTIF function.
  4. 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).

07
of 07

Adding the Search Criteria

Excel 2010 COUNTIF Function Tutorial
Excel 2010 COUNTIF Function Tutorial. © Ted French

Adding the Search Criteria

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 do this we enter > 250 into D12 - the cell identified in the function as containing the criteria argument.

Tutorial Steps

  1. In cell D12 type > 250 and press the Enter key on the keyboard.
  2. The number 4 should appear in cell E12.
  3. The criterion of " > 250 " is met in four cells in column E: E4, E5, E8, E9. Therefore these are the only cells counted by the function.
  4. When you click on cell E12, the complete function
    =COUNTIF (E3:E9, D12) appears in the formula bar above the worksheet.