Count Data that Meets Specific Criteria with Excel's COUNTIFS Function

Excel's COUNTIFS function can be used to count up the number of data records in a selected range that match specific criteria.

COUNTIFS extends the usefulness of the COUNTIF function by allowing you to specify from 2 to 127 criteria rather than just one as in COUNTIF.

Normally, COUNTIFS 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.

COUNTIFS looks for specific criteria in two or more fields in the record and only if it finds a match for each field specified is the record counted.

01
of 09

COUNTIFS Function Step by Step Tutorial

Excel COUNTIFS Function Step by Step Tutorial
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

In the COUNTIF step by step tutorial we matched the single criterion of sales agents who had sold more than 250 orders in a year.

In this tutorial, we will set a second condition using COUNTIFS - that of sales agents in the East sales region who made more than 250 sales in the past year.

Setting additional conditions is done by specifying additional Criteria_range and Criteria arguments for COUNTIFS.

Following the steps in the tutorial topics below walks you through creating and using the COUNTIFS function seen in the image above.

Tutorial Topics

  • Entering the Tutorial Data
  • The COUNTIFS Function's Syntax
  • Starting the COUNTIFS Function
  • The Criteria_range1 Argument
  • The Criteria1 Argument
  • The Criteria_range2 Argument
  • The Criteria2 Argument
  • And the Answer is ..
02
of 09

Entering the Tutorial Data

Excel COUNTIFS Function Step by Step Tutorial
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

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

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

In row 12 below the data we will add the COUNTIFS function and the two search criteria:

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

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 COUNTIFS function will give you the same results.

03
of 09

The COUNTIFS Function's Syntax

Excel COUNTIFS Function Step by Step Tutorial
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

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 / Criteria pairs can be specified in the function.

The COUNTIFS Function's Arguments

The function's arguments tell COUNTIFS what criteria we 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 we are trying to match in the data record. Actual data or the cell reference to the data can be entered for this argument.

04
of 09

Starting the COUNTIFS Function

Excel COUNTIFS Function Step by Step Tutorial
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

Although it is possible to just type the COUNTIFS function and its arguments 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 F12 to make it the active cell. This is where we will enter the COUNTIFS function.
  2. Click on the Formulas tab.
  3. Choose More Functions > Statistical from the ribbon to open the function drop down list.
  4. Click on COUNTIFS in the list to bring up the function's dialog box.

The data that we enter into the blank lines in the dialog box will form the arguments of the COUNTIFS function.

As mentioned, these arguments tell the function what criteria we are trying to match and what range of data to search to find these criteria.

05
of 09

Entering the Criteria_range1 Argument

Excel COUNTIFS Function Step by Step Tutorial
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

In this tutorial we are trying to match two criteria in each data record:

  1. Sales agents from the East sales region.
  2. Sales agents who have more than 250 sales orders for the year.

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

Tutorial Steps

  1. In the dialog box, click on the Criteria_range1 line.
  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

Entering the Criteria1 Argument

Excel COUNTIFS Function Step by Step Tutorial
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

In this tutorial the first criteria we are looking to match is if data in the range D3:D9 equals East.

Although actual data - such as the word East - can be entered into the dialog box for this argument it is usually best to enter the cell reference to the data's location in the worksheet into the dialog box.

Tutorial Steps

  1. Click on the Criteria1 line in the dialog box.
  2. Click on cell D12 to enter that cell reference into the dialog box.
  3. The search term East will be added to cell D12 in the last step of the tutorial.

How Cell References Increase COUNTIFS Versatility

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

So after counting up the number of agents from the East region it will be easy to find the same data for another sales region simply by changing East to North or West in cell D12. The function will automatically update and display the new result.

07
of 09

Entering the Criteria_range2 Argument

Excel COUNTIFS Function Step by Step Tutorial
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

As mentioned previously, in this tutorial we are trying to match two criteria in each data record

  1. Sales agents from the East sales region.
  2. Sales agents who have made more than 250 sales this year.

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

Tutorial Steps

  1. In the dialog box, click on the Criteria_range2 line.
  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

Entering the Criteria2 Argument

Excel COUNTIFS Function Step by
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

Entering the Criteria2 Argument and Completing the COUNTIFS Function

In this tutorial the second criteria we are looking to match is if data in the range E3:E9 is greater than 250 sales orders.

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

Tutorial Steps

  1. Click on the Criteria2 line in the dialog box.
  2. Click on cell E12 to enter that cell reference. The function will search the range selected in the previous step for data that matches this criteria.
  3. Click OK to complete the COUNTIFS function and close the dialog box.
  4. An answer of zero ( 0 ) will appear in cell F12 - the cell where we entered the function - because we have not yet added the data to the Criteria1 and Criteria2 fields (C12 and D12). Until we do, there is nothing for COUNTIFS to count and so the total stays at zero.
  5. The search criteria will be added in the next step of the tutorial.
09
of 09

Adding the Search Criteria and Completing the Tutorial

Excel COUNTIFS Function Step by Step Tutorial
Excel COUNTIFS Function Step by Step Tutorial. © Ted French

The last step in the tutorial is to add data to the cells in the worksheet identified as containing the Criteria arguments.

Tutorial Steps

  1. In cell D12 type East and press the Enter key on the keyboard.
  2. In cell E12 type >250 and press the Enter key on the keyboard (the " > " is the symbol for greater than in Excel) .
  3. The answer 2 should appear in cell F12.
  4. Only two agents - Ralph and Sam - work in the East sales region and made more than 250 orders for the year, therefore, only these two records are counted by the function.
  5. Even though Martha works in the East region, she had fewer than 250 orders and, therefore, her record is not counted.
  6. Similarly, Joe and Tom both had more than 250 orders for the year, but neither works in the East sales region so their records are not counted either.
  7. When you click on cell F12, the complete function
    =COUNTIFS (F3:F9,D3:D9,D12,E3:E9,E12) appears in the formula bar above the worksheet.
Was this page helpful?