Count criteria for matching data with Excel COUNTIF function

Use the COUNTIF function in Excel to count the number of data records in the selected range. COUNTIF searches for specific criteria in two or more fields in each entry, and counts that entry only if it finds a match for each criteria you specify.

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

Content
  1. COUNTIF Walkthrough function
  2. Enter training data
  3. COUNTIF Function Syntax
  4. Execute COUNTIF function
  5. self study
  6. Enter the argument Criteria_range1
  7. self study
  8. Enter Criteria1 argument
  9. self study
  10. Enter the argument Criteria_range2
  11. self study
  12. Enter criteria2 Arguments
  13. self study
  14. Full tutorial
  15. How COUNTIF logic works

COUNTIF Walkthrough function

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

The COUNTIF function works with rows of data, called records. A record has associated data in every cell or field in a row, such as company name, address, and phone number.

Learn how the COUNTIF function works in the COUNTIF walkthrough, which uses a single criterion to determine which sales agents have sold more than 250 orders per year.

This tutorial specifies the second condition using the COUNTIF function . . This second criterion limits those sellers in the eastern sales region who have made more than 250 sales in the past year.

The COUNTIF function can do this because it allows multiple ranges (Criteria_range ) and a number of conditions (criteria).

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

Enter training data

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

For this tutorial, enter the data shown in the image above into cells D1 through F11 in an Excel worksheet.

On line 12, below the data is the COUNTIF function and two search criteria. These are the criteria:

  • Sales agents from the East sales region

  • More than 250 orders per year

To set this criterion, type East in a cell D12 and > 250 in a cell E12 .

COUNTIF Function Syntax

In Excel, function syntax refers to the layout of the function and includes the function name, parentheses, and arguments.

The syntax for the COUNTIF function is:

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

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

The function arguments tell COUNTIF what criteria you are trying to meet and what range of data to search in to find those criteria.

All arguments in this function are required.

Criteria_range : The group of cells in which the function should look for a match with the corresponding Criteria argument.

criteria . The value you are trying to match in the data entry. For this argument, you can enter actual data or a reference to a data cell.

Using COUNTIF to specify multiple ranges and multiple criteria limits the number of records you want to count much more than the COUNTIF function.

Execute COUNTIF function

While it is possible to enter the COUNTIF function and its arguments in a cell on a worksheet, it is easier to enter the function using the function’s dialog box.

self study

  1. Select cell F12 to make it active. Here the COUNTIF function is introduced.

  2. Select formulas .

  3. Select Additional Features > Statistics to open a list of statistical functions.

  4. Select COUNTIF to open the Function Arguments dialog box.

The data entered on the empty lines in the dialog are the arguments for the COUNTIF function.

Enter the argument Criteria_range1

In this guide, there are two criteria to be compared with each data entry:

  • Sales agents from the East sales region

  • Sales agents with more than 250 sales orders per year

The Criteria_range1 argument specifies the range of cells that the COUNTIF function should search for when searching for the first criterion, which is the East sales region.

self study

  1. In the “Function Arguments” dialog box, place the cursor in the “Criteria_range1” text box.

  2. Highlight cells D3 to D9 on the worksheet to enter those cell references as the range the function looks for.

Enter Criteria1 argument

In this tutorial, the first criterion is whether data in the D3:D9 range is Eastern.

Although factual data, such as the word East , it is usually best to refer to the data cell in the dialog box.

self study

  1. Place the cursor in the Criteria1 text field of the Function Arguments dialog box.

  2. Select cell D12 to enter a reference to that cell in the dialog box.

  3. Search term East added to cell D12.

When a cell reference, such as D12, is entered as the Criteria argument, the COUNTIFS function matches the data entered in that cell on the worksheet.

Since the cell reference is used for the criteria, after counting the agents from the eastern region that have more than 250 sales, it is easy to find the same data for another sales region by changing the data in cell D12 (for example, from eastern to the north). or west). The function automatically updates and displays the new result.

Enter the argument Criteria_range2

The Criteria_range2 argument specifies the range of cells that the COUNTIF function should evaluate when it tries to match the second criterion. In this lesson, sales agents sold over 250 orders this year.

self study

  1. In the “Function Arguments” dialog box, place the cursor in the “Criteria_range2” text box.

  2. Highlight cells from E3 to E9 on the worksheet to enter those cell references as the second range the function looks for.

Enter criteria2 Arguments

In this tutorial, the second criterion is met if the data in the E3:E9 range exceeds 250 sales orders.

As with the Criteria1 argument, you enter the cell reference for the Criteria2 location in the dialog box, not the data itself.

self study

  1. Place cursor in text field Criteria2 in the Function Arguments dialog box.

  2. Select cell E12 to enter a reference to that cell. The function searches the range selected in the previous step to find data that meets this criterion.

  3. Select Okay to end the COUNTIF function and close the dialog box.

  4. The answer two (2) appears in cell F12, the cell where you entered the function.

Full tutorial

The COUNTIF function only searched for sellers in the East region and counted the number of sellers who had made more than 250 sales. In this example, there were two reps who did this.

How COUNTIF logic works

The sequence of logic used for the COUNTIF function to perform this calculation is as follows.

  • Only two agents, Ralph and Sam, operate in the Eastern Sales Region and have placed more than 250 orders per year. Only these two entries are considered by the feature.

  • Although Martha works in the East region, she has fewer than 250 orders. Her entry doesn’t count.

  • Joe and Tom have had over 250 orders this year, but none of them work in the Eastern sales region. Their submissions do not count.

When cell F12 is selected, the entire function appears in the formula bar above the worksheet. It looks like this:

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

Change the search region to North, South, or West, or change #Orders to another number. Watch how the results change depending on the search criteria entered in these cells.

Leave a Reply

Your email address will not be published.