Sum Amounts With 2 Criteria SUMIFS Video

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.

Video: Sum Amounts With 2 Criteria SUMIFS

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.

Video Timeline

• 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

Video Transcript

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.

List of Orders

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.

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'.

sum_range

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

criteria_range1

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

criteria1

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.

criteria_range2

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

Select those cells, a comma.

criteria2 Operator

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.

criteria2 Value

Our value, follows the ampersand, which is 10,

And we'll close the bracket.

SUMIFS Result

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.

Use Cell References

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.

Change the Value

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 Workbook

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.

____________

More Tutorials

Sum Functions -- Sum Cells

COUNT / COUNTIF