SUMIFS Function in Excel (+ video tutorial)

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

If you need to sum by criteria, the SUMIFS function in Excel is what you’ll want to use. With it, you can sum multiple criteria – or just a single criteria.

How to use SUMIFS in Excel

Click to Watch: SUMIFS Function in Excel – Tutorial

How Excel SUMIFS Works

SUMIFS sum data after it filters it by criteria (that you set).

SUMIFS has 3 required parts. They are:

1. sum_range: this is the numerical values that will be summed. For example, revenue, expenses, quantity, etc.
2. criteria_range1: is the data range in which Excel will look for the criteria. For example, if you want to sum the total revenue for employee John, then criteria_range1 will likely be a column with employee names.
3. criteria1: is the criteria that will be looked up in the criteria_range. For example, if you need the total revenue for employee John, then the criteria you will search for is the name “john.”

Criteria Ranges and Criterias work as pairs and each is numbered, making them easier to identify.

You can create up to 127 pairs of criteria in your SUMIFS function.

Let’s walk through some examples.

Create a SUMIFS with a Single Criteria

Let’s say, using the data in the image below, that you want to sum the total sales amount for employee Emerson.

Your SUMIFS function would look like this:

=SUMIFS(D2:D25,A2:A25,”Emerson”)

Here’s why:

D2:D25 for the sum_range

The first argument is the sum_range and refers to the range of values that will be summed.

In this example, we need to calculate total sales. So the data range that we need to sum is the Sales column.

Therefore, we used cells D2:D25 for the sum_range.

A2:A25 for criteria_range1

The criteria_range field will be the data range in which Excel will look for the item that you want to filter by.  

In this example, because we want to sum the sales amount for a specific employee the criteria_range will be the Employee column.

So, we used range A2:A25 for criteria_range1.

“Emerson” for criteria1:

Next, we need to specify what criteria to filter the calculation by. We’ll enter this in the criteria field.

The criteria_range and the criteria fields work as a pair. Once you enter your criteria, Excel will look for it in the criteria_range.

In this example, setting “Emerson” as the criteria will return the total sales for employee Emerson.

“Emerson” will need to be written with the quotation marks since it is text.

TIP: To avoid formula errors, always use quotation marks around any text in Excel formulas.

Press Enter and the result is $19,365.

TIP: If you’re not sure of a spelling, you can use wildcard characters in your criteria

Create a SUMIFS with Multiple Criteria

SUMIFS makes it easy to sum by multiple criteria – it was created with this in mind!

Did you notice that in our last example we used criteria_range1 and criteria1? The “ 1 “ symbolizes that this was the first criteria.

To add a second criteria, you’ll need to extend your SUMIFS function to include criteria_range2 and criteria2.

You can create up to 127 pairs of criteria in a single SUMIFS formula! Each criteria pair (criteria_range + criteria) will be numbered to help you keep track.

Example of SUMIFS with Multiple Criteria

Let’s say you want to see Emerson’s total sales of shirts.

To add the next set of criteria, come to the end of your formula and add a comma. This will activate the next pair of criteria fields.

Your formula would look like:

=SUMIFS(D2:D25,A2:A25,”Emerson”,C2:C25,”Shirts”)

Here’s why:

To filter by shirts, Excel will need to reference the Products column. This means that criteria_range2 will need to be the cells in the Product column, C2:C25.

Then, to filter the Products column, use “shirts” (in quotation marks) as criteria2.

The result of this $11,142.

Important Notes About SUMIFS to Avoid Errors

With SUMIFS, you can include data ranges that are not adjacent to one another.

They can even be in separate sheets! Regardless of the location of your data ranges, there are a few important things to keep in mind:

Use Same Size Data Ranges

Each data range referenced in SUMIFS needs to be the same number of cells.

For example, if your criteria range contains 10 cells, but your criteria contains 9, you will get an error.

One way you can help avoid this is by converting your data to a Table (Ctrl+T keyboard shortcut).

Once it’s in a Table format, Excel automatically expands any ranges referenced in that Table.

Sort Your Data the Same

Be extra cautious if you’re referencing data ranges from different locations – for example from different sheets or workbooks.

If referencing from separate locations, it’s critical that each data range be sorted in the same order to avoid pulling the wrong results.

For example, you would not want to sort one list of employees alphabetically and the other list of employees by start date. Doing so will lead to incorrect formula results.

---

Ready to practice??? Download this spreadsheet to try the examples outlined here and in the tutorial.

---

---

FYI, this was created using the desktop version of Excel in Microsoft 365.

---