The SUMIFS function in Excel allows you to sum the values in a range of cells that meet multiple criteria. For example, you might use the SUMIFS function in a sales spreadsheet to to add up the value of sales of a specified product by a given sales person (e.g. the value of all sales of a microwave oven made by John). This lesson shows you how to use SUMIFS in your own spreadsheets.

Note that SUMIFS was introduced in Excel 2007. This lesson therefore only applies to Excel 2007, 2010, 2011 and 2013.

## Syntax

The syntax for the Microsoft Excel **SUMIFS function** is:

```
```**SUMIF( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )**

### Parameters or Arguments

*sum_range* is the cells to sum.

*criteria_range1* is the range of cells that you want to apply *criteria1* against.

*criteria1* is used to determine which cells to add. *criteria1* is applied against *criteria_range1*.

*criteria_range2, … criteria_range_n* is optional. It is the range of cells that you want to apply *criteria2, … criteria_n* against. There can be up to 127 ranges.

*criteria2, … criteria_n* is optional. It is used to determine which cells to add. *criteria2* is applied against *criteria_range2, criteria3* is applied against *criteria_range3*, and so on. There can be up to 127 criteria.

## Applies To

The **SUMIFS function** can be used in the following versions of Microsoft Excel:

- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007

## Type of Excel Function

The **SUMIFS function** can be used in Microsoft Excel as the following type of function:

- Worksheet function (WS)

## Example (as Worksheet Function)

Let’s look at some Excel SUMIFS function examples and explore how to use the **SUMIFS function** as a worksheet function in Microsoft Excel:

Based on the spreadsheet above, the following Excel **SUMIFS examples** would return:

=SUMIFS(C2:C9,A2:A9,"=2013")Result:40.05 (applies 1 criteria) =SUMIFS(C2:C9,A2:A9,"=2013",B2:B9,"=Oranges")Result:25.7 (applies 2 criteria) =SUMIFS(C2:C9,A2:A9,">=2009",B2:B9,"=Oranges", A2:A9,"<=2012")Result:4.55 (applies 3 critiera) =SUMIFS(C2:C9,A2:A9,">=2009",B2:B9,"=B*")Result:18.85 (Uses the * wildcard to match on all products that start with B) =SUMIFS(C2:C9,A2:A9,">=2009",B2:B9,"=B?nanas")Result:18.85 (Uses the ? wildcard to match on a single character, ie: Bananas, Benanas, Binanas, Bonanas, and so on)

### Using Named Ranges

You can also use a named range in the **SUMIFS function**. For example, we’ve created a named range called *cost* that refers to **column C** in Sheet 1.

Then we’ve entered the following data in Excel:

Based on the spreadsheet above, the following Excel **SUMIFS examples** would return:

=SUMIFS(Cost,A2:A9,">2011",B2:B9,"=Apples")Result:5.1 =SUMIFS(Cost,A2:A9,"<2014",B2:B9,"=Oranges")Result:30.25

To view named ranges: Select the **Formulas tab** in the toolbar at the top of the screen. Then in the **Defined Names group**, click on the Defined Names drop-down and select *Name Manager*.

The *Name Manager* window should now appear.

## Frequently Asked Questions

Question:In Microsoft Excel 2003, I’m trying to work out a formula for the following:

If any row in column a = ‘jim’ AND any row in column b = ‘ben’ AND any row in column c = ‘alice’ then add together column d for all the rows that match the criteria. I can’t seem to get my ANDs and IFs formula right!

Answer:You should be able to create this formula without using the IF function or AND function. In Excel 2007 and higher, you could do this with the following **SUMIFS formula**:

=SUMIFS(D1:D10,A1:A10,"=jim",B1:B10,"=ben",C1:C10,"=alice")

This will evaluate the first 10 rows of data in your spreadsheet. You may need to adjust the ranges accordingly.

But since you are using Excel 2003 and the **SUMIFS function** has yet been implemented by Microsoft, you will need to use an array formula. So instead you can create the following array formula:

=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))

When creating your array formula, **you need to use Ctrl+Shift+Enter** instead of Enter. This creates {} brackets around your formula as follows:

{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}

This will perform the exact same calculations as the **SUMIFS function** above, but will work for versions of Excel 2003 or earlier.

*Courtesy: techonthenet.com*

## No Responses Yet