Quick, clean, and to the point

How to use the SUMIFs function

In this video, we'll look at How to use the SUMIFs function to sum cells that meet multiple criteria.

Let's take a look.

I'll enter formulas in column H to handle the conditions in column F.

In the first set of tables, we're using the named range called "number" and a named range called "color".

SUMIFS has 3 required arguments: sum_range, criteria_range1, and criteria1. After that you can enter additional range and criteria pairs to add additional conditions.

To SUM all numbers equal to 15, I enter the range "number" for both the sum_range and criteria_range1 and 15 for criteria1. When you use a number without an operator, there's no need to use double quotes.

To SUM all numbers with a color of blue, sum_range is "number", criteria_range1 is "color", and criteria is "blue".

In the next example, sum_range is "number", but I need to enter two pair of range and criteria to satisfy both conditions. Excel returns 30.

The next example also requires two pair of range and criteria. One to test for numbers equal to 10, and the other to test for a color of blue.

The final two problems involve summing numbers where the color is blue or purple. SUMIFS doesn't support OR conditions directly, so the easiest way to handle this is to write to SUMIF formulas and simply add them together.

Alternately, you can use enclose blue and purple in curly braces and feed them into the formula as criteria1. However, you'll then need to wrap the entire SUMIFs function in a SUM function.

In the next set of examples, we are using three named ranges: "date", "product", and "amount".

For this first problem,  I enter the range "amount" for the sum_range, and the range "date" for criteria_range1. The criteria is ">1/1/2013"

In the next example, "amount" is again the sum_range. The criteria_range1 is the range "product" and the criteria is just "coffee" in double quotes.

In the last example, "amount" is again the sum_range. However, I need to enter 3 additional range and criteria pairs to sum amounts where the product is "tea" and the date is in 2014.

criteria_range1 is "product", and criteria1 is "tea"

criteria_range2 is "date", and criteria2 is ,">=1/1/2014"

criteria_range3 is "date", and criteria3 is ,","<=12/31/2014"

If you need to do a lot of conditional summing with data, make sure you check out pivot tables. They make it easy to generate both conditional sums and counts even with very large sets of data.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.