Count Data that Meets Criteria with the Excel COUNTIFS Function

Image of person writing on note pad and working on laptop

 Towfiqu Photography/Getty Images

Use the COUNTIFS function in Excel to count the number of data records in a selected range. COUNTIFS looks for specific criteria in two or more fields in each record and only counts that record if it finds a match for each criterion you've specified.

The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel Online, and Excel for Mac.

01
of 09

COUNTIFS Function Step by Step Tutorial

A screenshot of an example using the COUNTIFS function.

COUNTIFS extends the usefulness of the COUNTIF function by allowing you to specify multiple ranges and multiple criteria, rather than just one as in the COUNTIF founction.

The COUNTIFS function works with rows of data called records. In a record, the data in each cell or field in the row is related, such as a company's name, address, and phone number.

Find out how the COUNTIF function works in the COUNTIF step by step tutorial, where a single criteria is used to identify which sales agents sold more than 250 orders in a year.

In this tutorial, a second condition is set using the COUNTIFS function. This second criteria narrows down those sales agents in the East sales region who made more than 250 sales in the past year.

The COUNTIFS function can do this because it allows you to specify multiple ranges (Criteria_range) and multiple conditions (Criteria) as arguments.

Follow the steps in this tutorial to create and use the COUNTIFS function in the image above.

02
of 09

Enter the Tutorial Data

Screenshot of setting up data for COUNTIFS function.

The first step to using the COUNTIFS function in Excel is to set up the data.

For this tutorial enter the data seen in the image above into cells D1 to F11 of an Excel worksheet.

Row 12, below the data, is where the COUNTIFS function and the two search criteria will be added. This is the criteria:

  • Sales agents from the East sales region
  • More than 250 orders placed for the year

To set up this criteria, type East into cell D12, and >250 into cell E12.

03
of 09

The COUNTIFS Function Syntax

A screenshot of COUNTIFS function 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 COUNTIFS function is:

=COUNTIFS(Criteria_range1,Criteria1,Criteria_range2,Criteria2, ...)

Up to 127 Criteria_range and Criteria pairs can be specified in the function.

The function's arguments tell COUNTIFS what criteria you are trying to match and what range of data to search to find these criteria.

All arguments in this function are required.

Criteria_range: The group of cells the function is to search for a match to the corresponding Criteria argument.

Criteria: The value you are trying to match in the data record. Actual data or the cell reference to the data can be entered for this argument.

Using COUNTIFS to specify multiple ranges and multiple criteria narrows down which records you want to count much more than the COUNTIF function.

04
of 09

Start the COUNTIFS Function

A screenshot of choosing the COUNTIFS function from the Excel menu.

Although it is possible to type the COUNTIFS function and its arguments into a cell in a worksheet, it may be easier to use the function's dialog box to enter the function.

Tutorial Steps

  1. Select cell F12 to make it the active cell. This is where the COUNTIFS function will be entered.
  2. Select Formulas.
  3. Select More Functions > Statistical to open the list of statistical functions.
  4. Select COUNTIFS to open the Function Arguments dialog box.

The data entered into the blank lines in the dialog box form the arguments of the COUNTIFS function.

05
of 09

Enter the Criteria_range1 Argument

Screenshot of entering criteria_range1 argument into COUNTIFS function

In this tutorial, the two criteria in each data record that will be matched are:

  • Sales agents from the East sales region
  • Sales agents who have more than 250 sales orders for the year

The Criteria_range1 argument indicates the range of cells for the COUNTIFS function to search when trying to match the first criteria which is the East sales region.

Tutorial Steps

  1. In the Function Arguments dialog box, place the cursor in the Criteria_range1 text box.
  2. Highlight cells D3 to D9 in the worksheet to enter these cell references as the range to be searched by the function.
06
of 09

Enter the Criteria1 Argument

A screenshot showing entering criteria into the COUNTIFS function.

In this tutorial, the first criteria that will be matched is whether data in the range D3:D9 equals East.

Although actual data can be entered, such as the word East, into the dialog box, it's usually best to enter the cell reference to the data into the dialog box.

Tutorial Steps

  1. Place the cursor in the Criteria1 text box of the Function Arguments dialog box.
  2. Select cell D12 to enter that cell reference into the dialog box.
  3. The search term East is added to cell D12.

If a cell reference, such as D12, is entered as the Criteria argument, the COUNTIFS function looks for matches to whatever data has been typed into that cell in the worksheet.

Because a cell reference is used for criteria, after the agents from the East region who surpassed 250 sales have been counted, it's easy to find the same data for another sales region by changing the data in cell D12 (for example, change East to North or West). The function automatically updates and displays the new result.

07
of 09

Enter the Criteria_range2 Argument

A screenshot of entering criteria_range2 into COUNTIFS function.

The Criteria_range2 argument indicates the range of cells for the COUNTIFS function to search when trying to match the second criteria. In this tutorial, it is sales agents who have sold more than 250 orders this year.

Tutorial Steps

  1. In the Function Arguments dialog box, place the cursor in the Criteria_range2 text box.
  2. Highlight cells E3 to E9 in the worksheet to enter these cell references as the second range to be searched by the function.
08
of 09

Enter the Criteria2 Arguments

A screenshot showing entering criteria2 into the COUNTIFS function.

In this tutorial, the second criteria that will be matched is if data in the range E3:E9 is greater than 250 sales orders.

As with the Criteria1 argument, enter the cell reference to Criteria2's location into the dialog box rather than the data itself.

Tutorial Steps

  1. Place the cursor in the Criteria2 text box in the Function Arguments dialog box.
  2. Select cell E12 to enter that cell reference. The function searches the range selected in the previous step for data that matches this criteria.
  3. Select OK to complete the COUNTIFS function and close the dialog box.
  4. An answer of two (2) appears in cell F12, the cell where you entered the function.
09
of 09

Complete the Tutorial

A screenshot of the completed COUNTIFS tutorial spreadsheet.

The COUNTIFS function only searched through the sales reps working in the East region and tallied up the number of sales reps who made more than 250 sales. In this example, there were two reps who accomplished this.

How COUNTIFS Logic Works

The order of logic that takes place for the COUNTIFS function to accomplish this calculation is as follows.

  • Only two agents, Ralph and Sam, work in the East sales region and made more than 250 orders for the year. Only these two records are counted by the function.
  • Even though Martha works in the East region, she had fewer than 250 orders. Her record is not counted.
  • Joe and Tom both had more than 250 orders for the year but neither works in the East sales region. Their records are not counted.

When you select cell F12, the complete function appears in the formula bar above the worksheet. It looks like this:

=COUNTIFS(D3:D9,D12,E3:E9,E12) 

Change either the search region to North, South, or West, or change the #Orders to a different number. Watch how the results change depending on the search criteria entered into those cells.