Count data in selected cells with Excel COUNTIF function

The COUNTIF function combines the IF function and the COUNT function in Excel. This combination counts how often certain data has been found in the selected group of cells. The IF part of the function determines which data matches the specified criteria, and the COUNT part performs the count.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

  1. Excel COUNTIF Function Syntax
  2. Enter sample data
  3. Create a COUNTIF function
  4. Highlight the range argument
  5. Specify Criteria Argument
  6. Add search criteria

Excel 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:

 = СЧЕТЕСЛИ ( Диапазон ,  Критерии ) 

Function arguments tell the function what condition it is tested for and what range of data to consider when the condition is met.

Range : a group of cells whose function is to search.

criteria † The value compared to the data in the range cells. If a match is found, the cell in the range is considered. For this argument, you can enter actual data or a reference to a data cell.

Enter sample data

Follow the instructions in this article to create and use the COUNTIF function. In this example, the COUNTIF function counts the number of sellers with more than 250 orders.

The first step to use the COUNTIF function in Excel is data entry. Enter data in cells C1 to E11 of the Excel worksheet as shown in the image above. The COUNTIF function and search criteria (more than 250 orders) are added in line 12 below the data.

The tutorial instructions do not include formatting steps for the worksheet. Your worksheet will be different from the worksheet in the example, but the COUNTIF function will give you the same results.

Create a COUNTIF function

Although it is possible to enter the COUNTIF function in a cell on a worksheet, it is easier to enter the function by using the built-in COUNTIF function in Excel.

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

  2. Select tab formulas on the adhesive tape

  3. Select Additional Features Statistical data

  4. Select COUNTIF in the list to open the Function Arguments dialog box. Function Builder opens in Excel for Mac.

The data entered on the two blank lines in the dialog are the arguments for the COUNTIF function. These arguments tell the function what condition to test for and which cells to consider when the condition is met.

Highlight the range argument

The Range argument tells the COUNTIF function what group of cells to search for when searching for the specified criteria.

  1. In the Function Arguments dialog box (or Formula Builder if you’re using a Mac), place your cursor in the Range text box.

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

Specify Criteria Argument

The Criteria argument tells COUNTIF what data to find in the Range argument. While you can enter actual data for this argument (such as text or numbers, such as >250), it is best to enter a cell reference in the dialog box (such as D12) and enter the data you want to match in that cell. to be entered in the worksheet.

  1. Place the cursor in the Criteria text box.

  2. Select cell D12 to enter a reference to that cell. The function searches the range selected in the previous step for data that matches data entered in that cell.

  3. Click Okay when you’re done. Select on a Mac Finished to end the function.

A null response appears in cell E12 (the cell where the function was entered) because no data was added to the Criteria field (cell D12).

 = СЧЕТЕСЛИ (Е3: Е9, Д12) 

Add search criteria

The last step in the tutorial is to add the criteria that the feature will meet. In this case, the number of sellers with more than 250 orders per year is taken into account.

  1. Select cell D12 † This is the cell defined in the function as the cell containing the criteria argument.

  2. Enter > 250 and press Enter

  3. The number 4 appears in cell E12.

Leave a Reply

Your email address will not be published. Required fields are marked *