Description
The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.
Syntax
The syntax for the SUMIFS function in Microsoft Excel is:
SUMIF( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )
Parameters or Arguments
- sum_range
- The cells to sum.
- criteria_range1
- The range of cells that you want to apply criteria1 against.
- criteria1
- It is used to determine which cells to add. criteria1 is applied againstcriteria_range1.
- criteria_range2, ... criteria_range_n
- Optional. It is the range of cells that you want to apply criteria2, ... criteria_nagainst. There can be up to 127 ranges.
- criteria2, ... criteria_n
- Optional. It is used to determine which cells to add. criteria2 is applied againstcriteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.
Applies To
- Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007
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 Excel spreadsheet above, the following 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. A named range is a descriptive name for a collection of cells or range in a worksheet. If you are unsure of how to setup a named range in your spreadsheet, read our tutorial on Adding a Named Range.
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 Excel spreadsheet above, the following 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 orAND 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 ofEnter. 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.
