Excel COUNTIFS(): How to Count Cells with Multiple Criteria

The Excel COUNTIFS() function is a statistical function that is used to count the number of cells that meet one or more sets of specific criteria. For example, the function can be used to count how often a specific location appears in a list of sales for a specific salesperson. The function is similar to the Excel COUNTIF() function, though it lets you use more than a single condition.

In this tutorial, you’ll learn how to use the Excel COUNTIFS() function and how to customize it to meet your needs. You will learn all the basics of the function and even dive into some advanced uses of the function.

By the end of this tutorial, you’ll have learned the following:

• How to understand and how to use the Excel COUNTIFS() function
• How to resolve common problems that occur when using the Excel COUNTIFS() function
• What some of the best practices are when working with the COUNTIFS() function
• How to dive deeper into the function using custom use cases of the function

Understanding the Excel COUNTIFS() Function

Let’s take a look at the Excel COUNTIFS() function and its arguments to better understand how to use it:

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

We can see that the function has two required arguments and up to 254 optional arguments. Let’s take a look at these arguments in more detail:

Argument	Description	Required?
criteria_range1=	The range of cells to apply the condition to	Yes
criteria1=	The condition to apply, including any of the logical operators that may be required	Yes
[criteria_range2, criteria2]	The additional range of cells to look apply the conditions to and the conditions to apply	No

The function has two required parameters. However, if you only use the first two parameters, the function behaves in the exact same way as the Excel COUNTIF() function.

In order to add more conditions, you need to add pairs of criteria and ranges. This means that even if you’re applying multiple conditions to the same range, you need to add the range multiple times.

Similar to the Excel COUNTIF() function, the criteria can include logical, wildcards, or partial matches. Let’s break down how these criteria can work:

1. Logical operators can check for a logical condition, including the following operators: <, >, <=, >=, and =.
2. Wildcard operators let you represent different types of strings. The ? operator replaces any single character, while the * operator replaces any number of any character.

Let’s now dive into how to use the Excel COUNTIFS() function to count values that match multiple conditions.

How to Use the Excel COUNTIFS() Function

In this section, you’ll learn how to use the Excel COUNTIFS() function to count cells that meet multiple conditions. Similar to the Excel COUNTIF() function, you can pass in a single range and condition. However, you can also pass in multiple conditions.

Let’s take a look at an example of how to use the Excel COUNTIFS() function:

Let’s break down what we’re doing in the example above:

1. We have a table containing sales data for different regions. Range B2:B13 contains the region that the sale was made in and range C2:C13 contains the amount sold.
2. In the second table, we want to count the number of sales in each region that was over $2,000.
3. In cell F2, we use the following formula: =COUNTIFS($B3:$B13, E3, $C$3:$C$13, ">2000").
4. When we hit the return key, the value 3 is returned.

Let’s break down the COUNTIFS() formula used above:

• We pass in the range of $B$3:$B$13 to be equal to the value in cell E3. This allows us to count records that are equal to “North”. Since we’re using absolute references, we can drag the formula down without worrying about modifying the range.
• For our second criteria and range, we use the range $C$3:$C$13 to be greater than 2000. It’s important to note that because we’re using a logical operator, we have to wrap the condition in double-quotes.

Now that we’ve learned how to use the COUNTIFS() function with a simple example, let’s dive into the different criteria options available within the function.

Understanding Different Criteria Options in the Excel COUNTIFS() Function

The Excel COUNTIFS() function provides a large variety of criteria options. Earlier, you learned that you can use logical and wildcard operators. This can include dates and cell references, as well.

Remember, that the benefit of the COUNTIFS() function is that you can combine multiple of these criteria together. This means that you using the options below, you can combine any of the logical operators below. These operators are chained together using logical AND. This means that the conditions must all be true for a value to be counted.

The table below breaks down the different options available to you in searching for different criteria in the COUNTIF() function:

Criteria Logic	Excel Criteria
Cells equal to “South”	“South”
Cells not equal to “South”	“<>South”
Cells starting with “South”	“South*”
Cells ending with “East”	“*East”
Cells greater than 500	“>500”
Cells greater than or equal to 500	“>=500”
Cells less than 500	“<500”
Cells less than or equal to 500	“<=500”
Cells equal to 500	500
Cells less than the value in A2	“<“&A2
Cells greater than today’s date	“<“&TODAY()
Cells not equal to today’s date	“<>”&TODAY()

While a lot of these probably make sense, some of them may stick out as odd. For example, when you’re using a function or a cell reference in your criteria, be sure to wrap the operator in a string. When you do this, you need to concatenate the string using the ampersand operator.

In the following section, you’ll learn how to tackle common problems when working with the COUNTIFS() function.

Common Problems When Working with Excel COUNTIFS()

When working with the COUNTIFS() function, you may encounter some problems. There are three main types of problems that you may run into when working with the function:

1. Incorrect counts returned for long strings. This error occurs when strings exceed 255 characters. Excel works best when the string is under that character limit.
2. No counts are returned when there should be. In order to resolve this, you should check if your conditions are wrapped in quotes, in order to close off the condition.
3. A #VALUE error is returned. If you’re referencing a value in another workbook, you need to make sure that the workbook is open. Excel expects that the workbook is open to be able to search within it.

The list above covers the main causes of problems when working with the function. Writing the criteria can be a bit awkward and can take some getting used to. Refer back to the table above to see how best to write your criteria.

Best Practices When Working with Excel COUNTIFS()

In this section, we’ll explore some of the best practices when working with the COUNTIFS() function. You can also think of some of these as tips and tricks that simply make the function more intuitive. Let’s dive right in!

COUNTIFS() is Case Insensitive

The COUNTIFS() function ignores casing, meaning that it doesn’t matter if your text is uppercase or lowercase. This can lead to some unexpected behavior, but can actually also make your function much simpler to use! For example, if you’re searching for North, it doesn’t matter if you search for NORTH, North, north, or even NoRtH! When you’re working with text data, the values may have some data entry errors. Because of this, text insensitivity can actually be quite helpful.

Named Ranges Make COUNTIFS() More Intuitive

Using named ranges can make working with the function significantly easier. Rather than using cell references and needing to worry about absolute references, using named ranges can make the function much simpler. If you think of the function of asking Excel to look in a certain range for certain criteria, this makes even more sense. Keep in mind that you’re also writing your function to be easily understood by future users of your workbook.

COUNTIFS() Works with “?” and “*” Wildcards

Using wildcards can make your criteria much stronger. Excel provides two wildcards that you can use in the COUNTIF() function: ? replaces any single character, while * replaces any number of characters. Knowing this can make counting different string values that much easier.

Additional Resources for the Excel COUNTIFS() Function

To learn more about related topics, check out the resources below:

• Excel COUNTIF(): How to Use the Excel COUNTIF() Function
• How to Count Not Blank Cells in Excel with COUNTIF()
• How to Count Cells That Contain Text in Excel
• Excel COUNTIFS() Function: Official Documentation