## Abstract

## Transcript

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.

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

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

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

To SUM all numbers equal to 15, I enter the range "number" for both the *sum_range *and* criteria_range1* and I enter "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 two SUMIF formulas and simply add them together.

As an alternative, you can 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 inside a SUM function.

In the next set of tables, we're using three named ranges: *"*date*",* *"*product*"*, and *"*amount.*"*

For the 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 three additional range and criteria pairs to sum amounts where the product is "tea" and the date is within 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 entered as "<=12/31/2014"

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