Use SUMIFS to sum cells that match multiple criteria in Excel

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