Excel SUMIFS(): How to Use the Excel SUMIFS() Function

The Excel SUMIFS() function is a logical function that is used to add values of cells that meet one or more sets of specific criteria. For example, the function can be used to add up values from a specific region that are above a certain amount. The function is similar to the Excel SUMIF() function, though it lets you use more than a single condition.

In this tutorial, you’ll learn how to use the Excel SUMIFS() 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 SUMIFS() function
How to resolve common problems that occur when using the Excel SUMIFS() function
What some of the best practices are when working with the SUMIFS() function
How to dive deeper into the function using custom use cases of the function

Table of Contents

Understanding the Excel SUMIFS() Function

What is the Excel SUMIFS() function?

The Excel SUMIFS() function is used to sum cells in a range of data that meet a single condition or multiple conditions. The function checks that all conditions passed into the function are true, using the logical AND operator. The function can check for logical operators, such as greater than, less than, or equal to. Similarly, the function can be used to check against cells that contain dates, numbers, and text.

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

=SUMIFS(sum_range, 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?
`sum_range=`	The range of cells you want to sum up	Yes
`criteria_range1=`	The range of cells to look for a condition in	Yes
`criteria1`	The criteria to use	Yes
[criteria_range2, criteria2]	The additional pairs of criteria and criteria ranges to look for	No

The arguments of the Excel SUMIFS() function

The function has three required arguments. If you only use three arguments, then function actually behaves in the same way as the Excel SUMIF() 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 SUMIF() function, the criteria can include logical, wildcards, or partial matches. Let’s break down how these criteria can work:

Logical operators can check for a logical condition, including the following operators: <, >, <=, >=, and =.
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 SUMIFS() function to add up values that meet multiple conditions.

How to Use the Excel SUMIFS() Function

In this section, you’ll learn how to use the Excel SUMIFS() function to count cells that meet multiple conditions. Similar to the Excel SUMIF() function, you can pass in a single range and condition, though you also need to pass in a sum range, even if it’s the same range. However, you can also pass in multiple conditions.

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

Let’s break down what we’re doing in the example above. We want to add up our sales by region, but only sales that were equal to or greater than $2,500. Imagine that these sales are eligible for additional bonuses.

In the first table, we have our regional sales data. In the second table, we have the calculated sales by region as placeholders.
In cell F3, we include our formula. We pass in the sum_range of equaling the range of data in the Sales column.
As our first condition, we use the range of B3:B13, which contains our Region data. We pass in the criteria that it’s equal to North.
As our second condition, we use the range of C3:C13, which contains our Sales data. In this case, we pass in the criteria that it’s greater than or equal to $2,500. Note how we’re wrapping the operator in double quotes and then concatenating the value. Without this, the formula would raise an error.

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 SUMIFS() Function

The Excel SUMIFS() 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 SUMIFS() 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 SUMIFS() 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()

How to use different criteria in the Excel SUMIFS() function

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 SUMIFS() function.

Common Problems When Working with Excel SUMIFS()

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

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.
No sums 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.
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 SUMIFS()

In this section, we’ll explore some of the best practices when working with the SUMIFS() 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!

SUMIFS() 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 SOUTH, South, south, or even SoUtH! 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 SUMIFS() 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.

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

Using wildcards can make your criteria much stronger. Excel provides two wildcards that you can use in the SUMIFS() 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 SUMIFS() Function

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