totn Excel Functions

MS Excel: How to use the SUMIFS Function (WS)

This Excel tutorial explains how to use the Excel SUMIFS function with syntax and examples.

Description

The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

The SUMIFS function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMIFS function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the SUMIFS function in Microsoft Excel is:

SUMIFS( 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 against criteria_range1.
criteria_range2, ... criteria_range_n
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
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.

Returns

The SUMIFS function returns a numeric value.

Applies To

  • Excel for Office 365, Excel 2019, 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:

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.

Microsoft Excel

Then we've entered the following data in Excel:

Microsoft 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.

Microsoft Excel

The Name Manager window should now appear.

Microsoft Excel

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.

Share on: