This video shows how to use the SUMIFS function to sum the amounts in rows that meet two criteria. Download the free Excel workbook to follow along with the video.

Watch this short video to see how to use the SUMIFS function to sum the amounts in rows that meet two criteria. The transcript is below the video, and you can download the Excel workbook below, to follow along with the video.

For the written steps and more examples, go to the Excel Sum Function Examples page.

- 00:00 Introduction
- 00:11 List of Orders
- 00:26 SUMIFS Formula
- 00:36 sum_range
- 00:41 criteria_range1
- 00:47 criteria1
- 00:57 criteria_range2
- 01:05 criteria2 Operator
- 01:20 criteria2 Value
- 01:26 SUMIFS Result
- 01:36 Use Cell References
- 01:56 Change the Value
- 02:08 Get the Workbook

Hey, this is Sarah from Contextures.com. Let's take a look at how we can use the SUMIFS formula to calculate a total based on values in two or more columns.

In the example here, we're looking at a summary of orders that have been placed, are pending, and have been shipped.

I want the amount totalled for all of our shipped orders, with 10 or more units. For this, we'll build a SUMIFS formula.

So, we'll select the cell, head to the formula bar, and start with equals SUMIFS, our open bracket, then we're going to select the 'sum_range'.

Here, we want to see the amount, so we'll select those cells, and follow with a comma.

Now it wants to see the first criteria. So we'll select our status, comma.

And now, what is the criteria? For us, we're looking for shipped.

So we'll do quotation "Shipped" and we'll close the quote, followed by a comma.

Next up, we're going to head to our second criteria, which will be the unit number.

Select those cells, a comma.

We're going to use something called an operator.

So again, we want an open quote. We're going to let it know that we would like to see anything greater than or equal to 10.

So we'll type in greater than, and the equal symbol and close the quote.

Our value, follows the ampersand, which is 10,

And we'll close the bracket.

By pressing Enter, we're going to see our amount is 827.

So that reflects the total shipped, with units that are greater than or equal to 10.

To make it more flexible, we could replace where we've typed, "Shipped" and the number "10", by what we already have here in these cells.

- So I'm going to head back to that formula. I'm going to select "Shipped", and I'm going to replace it with this cell.
- I'll do the same thing for the number 10, and we'll press Enter.

Now, if we change the value in here, it will change our amount totaled.

So if we change this to 2, we're now going to see additional orders that have been shipped.

--------

Thanks for watching this video. You can go to my website, contextures.com to get this workbook, and please subscribe to my Contextures YouTube channel, so you can see the latest videos as I post them.

Get the SUMIFS Orders sample workbook, to follow along with the Sum Amounts with 2 Criteria video. The zipped file is in xlsx format, and does not contain any macros.

____________

Last updated: July 15, 2021 11:37 AM