Unlock the Power of Excel’s Secret Function and Never Struggle with Counting Data Again!

As one of the most widely used software in the world, Microsoft Excel has become an essential tool for businesses, professionals, and students alike. While it is well-known for its basic features such as calculating, sorting, and filtering data, it also contains several lesser-known functions that can take your data analysis to the next level. In this article, we will focus on one such powerful function that can save you time and effort: the ‘COUNTIFS’ formula.

What is COUNTIFS?

As the name suggests, ‘COUNTIFS’ is the plural version of the ‘COUNTIF’ function that counts the number of cells in a range that meet a specific criterion. For instance, if you want to determine how many times the word ‘apple’ appears in a column, you can use the formula =COUNTIF(A:A,“apple”), where ‘A:A’ is the range of cells you want to search in, and ‘apple’ is the criterion. However, when you have multiple criteria to check, such as counting how many times a specific product was sold in a particular month and region, you may need to use the ‘COUNTIFS’ function.

The ‘COUNTIFS’ function counts the number of cells that meet two or more criteria. It follows the syntax:

COUNTIFS(range1, criteria1, [range2], [criteria2], ...)

The ‘‘range’’ argument is the range of cells you want to search in, and the ‘‘criteria’’ argument sets the condition that must be met. You can add additional pairs of ‘range-criteria’ as needed, separated by a comma.

Using COUNTIFS to Count Data

Let’s assume you are managing a sales department, and you want to calculate how many units of a product were sold in a specified month and region. You have a table with columns ‘Product,’ ‘Region,’ ‘Month,’ and ‘Units Sold.’ To count the units sold in January for product ‘A’ in the ‘East’ region, you can use the formula:

=COUNTIFS(B2:B10,"A",C2:C10,"East",D2:D10,MONTH("1/1/2022"))

Here, ‘B2:B10’ represents the ‘Product’ column, ‘C2:C10’ represents the ‘Region’ column, and ‘D2:D10’ represents the ‘Month’ column. The ‘MONTH(“1/1/2022”)’ function extracts the month value from a date, as specified in the criteria. Note that you need to enclose text values in double quotes and specify the date format correctly, or else you may get an error.

Similarly, you can use the ‘COUNTIFS’ function to count data based on various combinations of criteria, such as:

  • Counting the number of customers who made a purchase between a specified date range
  • Counting the total sales revenue generated by a specific salesperson in a particular quarter
  • Counting the number of employees who belong to a specific department and earn more than a certain salary range
  • Counting the frequency of a word present in multiple cells and meeting certain formatting criteria, etc.

By using ‘COUNTIFS,’ you can avoid the need to sort or filter your data manually, which can be time-consuming and prone to errors. You can also use it to create dynamic reports that update automatically based on changing criteria.

Conclusion

The ‘COUNTIFS’ function in Excel is a valuable tool for anyone who deals with a large amount of data or needs to perform complex calculations quickly. By unlocking its potential, you can speed up your data analysis process and gain a deeper insight into your business or personal projects. With some practice, you can master this and other powerful Excel functions and become an expert in data management.

This article explains how to use the COUNTIF function in selected cells in Excel 2019, 2016, 2013, 2010, 2007, Excel for Microsoft 365, Excel Online, for Mac, iPad, iPhone, and Android.

What to Know

  • Syntax: “=COUNTIF([range],[criteria])” where range = group of cells and criteria = value with range data.Set up: Enter example data > select cell > Formulas tab > More Functions > Statistical > COUNTIF.Highlight range: Place cursor in Range text box in Function Arguments dialog box > select cells.

Excel COUNTIF 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 COUNTIF function is:

=COUNTIF(Range,Criteria)

The function’s arguments tell the function what condition is being tested for and what range of data to count when the condition is met.

  • Range: The group of cells the function is to search.
  • Criteria: The value compared with the data in the Range cells. If a match is found, the cell in the Range is counted. Actual data or the cell reference to the data can be entered for this argument.

Enter the Example Data

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

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 shown in the image above. The COUNTIF function and the search criteria (greater than 250 orders) will be added to row 12 below the data.

Build the COUNTIF Function

Although it is possible to type the COUNTIF function into a cell in a worksheet, it’s easier to use the built-in COUNTIF function in Excel to enter the function.

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

  • Select cell E12 to make it the active cell. This is where the COUNTIF function will be entered.
  • Select the Formulas tab of the ribbon.
  • Choose More Functions > Statistical.
  • Select COUNTIF in the list to open the Function Arguments dialog box. In Excel for Mac, the Function Builder opens.

The data entered into the two blank rows in the dialog box form the arguments of the COUNTIF function. These arguments tell the function what condition is being tested for and what cells to count when the condition is met.

Select cell E12 to make it the active cell. This is where the COUNTIF function will be entered.

Select the Formulas tab of the ribbon.

Choose More Functions > Statistical.

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

Highlight the Range Argument

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

  • In the Function Arguments dialog box (or the Formula Builder if you’re working on a Mac), place the cursor in the Range text box.
  • Highlight cells E3 to E9 on the worksheet to enter these cell references as the range to be searched by the function.

Specify the Criteria Argument

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

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

Highlight cells E3 to E9 on the worksheet to enter these cell references as the range to be searched by the function.

  • Place the cursor in the Criteria text box.
  • Select cell D12 to enter that cell reference. The function searches the range selected in the previous step for data that matches whatever data is entered into this cell.
  • Select OK when you’re finished. On a Mac, select Done to complete the function.

An answer of zero appears in cell E12 (the cell where the function was entered) because data has not been added to the Criteria field (cell D12).

Place the cursor in the Criteria text box.

Select cell D12 to enter that cell reference. The function searches the range selected in the previous step for data that matches whatever data is entered into this cell.

Select OK when you’re finished. On a Mac, select Done to complete the function.

=COUNTIF(E3:E9,D12)

Add the Search Criteria

The last step in the tutorial is to add the criteria the function will match. In this case, the number of Sales Reps with more than 250 orders for the year will be counted.

  • Select cell D12. This is the cell identified in the function as containing the criteria argument.
  • Type >250 and press Enter.
  • The number 4 appears in cell E12.

Select cell D12. This is the cell identified in the function as containing the criteria argument.

Type >250 and press Enter.

The number 4 appears in cell E12.

Get the Latest Tech News Delivered Every Day