How to use the COUNTIFS Function in Excel

When managing data in Excel, it’s common to need a quick way to count entries based on more than one condition. That’s where the COUNTIFS function in Excel becomes incredibly useful. Whether you’re sorting customer orders, tracking project tasks, or analysing survey results, COUNTIFS allows you to count exactly what you need with precision.

If you’re still getting comfortable with Excel formulas and functions, don’t worry — we have plenty of guides to help you build your skills step-by-step.

• Formula Basics in Excel
• Introduction to Functions in Microsoft Excel
• How to use Countif in Excel

COUNTIFS Syntax

All functions in Excel have a syntax. This refers to the way in which the function is formatted – in other words, where you need to place a bracket or a comma. Below is the Excel COUNTIFS syntax.

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The “criteria_range1” section within the syntax specifies where the data is located for the 1st criteria. The “criteria1” section is the actual value you want to count. It then continues allowing you to specify the 2nd, 3rd, 4th etc. You can use up to 127 different criteria combinations within this one function.

Create a COUNTIFS Function in Excel

Let’s step through how you can add the COUNTIFS function in Excel based on multiple criteria you specify. In my example, I want to count how many sales based on the sales Agent and the State. I don’t want to have to sort the data, I want this information to be dynamic. This means that if any of the data changes, the COUNTIFS function will update itself accordingly.

1. Open Microsoft Excel.
2. If you have an existing worksheet you want to use this function in, go ahead and open it. Otherwise, create a sample worksheet similar to the one below:

3. Place your cursor in the cell where the countifs will be displayed. E.g. cell G6.
4. Go to the Formulas tab on the Ribbon and click the More Functions button.
5. Choose the Statistical library and click COUNTIFS.
6. Alternatively, you can click the Insert Function button and search for COUNTIFS.
7. The Function Arguments box will now appear:

8. Place your cursor in the Criteria_range1 field, select the location of the cells are we need to count. In this instance, I want to count by the name, so I need to highlight cells A3:A26.
9. Use the collapse arrow if you need to minimise the Function Arguments box in order to be able to highlight the cell range:

10. Place your cursor in the Criteria1 field.
11. Next you need to specify which criteria, or in this case, name of staff member, you specifically want it to count. You can directly type the criteria into the field, or the better option is to cell reference the criteria. I have the criteria entered in cell E6. Click the cell which contains your criteria:

12. The option for Criteria_range2 will now be visible. Click in Criteria_range2.
13. The second criteria will be based on the State the sale was made. Highlight cells B3 to B26.
14. Click in the Criteria2 field and link to the cell containing the specific state you want it to count in F6.
15. You could continue and add a 3rd, 4th or 5th criteria. You can specify up to 127 different criterias.
16. Click OK.
17. The result is now displayed as 1 sales for the Darren Grant in NSW.

Copy the formula

In order to copy the formula, you will need to “lock”, or apply absolute cell referencing to the cell range containing the names and states. This means that when we copy the function, the cell references will remain locked on, or “absolute” and will not change.

1. Click back into the cell containing the COUNTIFS function.
2. Press F2 on the keyword to edit the cell or click into the Formula bar.
3. Place your cursor on the A3 cell reference (first cell in the Names range) and press F4 on the keyword which will automatically add the $ symbols into the cell reference, effectively locking it in place.
4. Repeat the process for the A26 cell reference (last cell in the Names range).
5. Your formula should look similar to the one below, with the criteria_range1 cells now locked in as absolute cell reference:

6. Repeat the process to lock in the cell references for cells B3 and B26.
7. Press Enter on the keyboard to complete the edit.
8. Now place your cursor on the bottom right corner of the cell where the small square is displayed (AutoFill tool):

9. Hold the left mouse button down and drag the formula down to the remaining cells so that the COUNTIFS is copied for all other sets of criteria.
10. The COUNTIFS function is now displayed for each of criteria:

11. If I want to test out the dynamic functionality, edit any of the criteria combinations to see the count function automatically update.

The COUNTIFS function is a powerful option for anyone working with complex data sets in Excel. By allowing you to specify multiple criteria, it provides greater flexibility and accuracy when analysing your information. Be sure to comment below with any questions.