SUMIFS Function

The SUMIFS function is like the SUMIF function however you can use multiple criteria to determine which cells in a range are included in the sum. SUMIFS enables you to have up to a total of 127 range/criteria pairs.

An IFS function enables you to create several range/criteria pairs used to select the data that meet the criteria. Once items that meet the criteria have been identified, the average of the corresponding values in the main range is calculated. You can have up to a maximum of 127 range/criteria pairs as you can only have 255 arguments in an Excel function.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments

Argument	Description
Sum_range	Required. This is the range of cells you want to sum up.
Criteria_range1	Required. The range that is tested using Criteria1 . Criteria_range1  and Criteria1  are a pair where Criteria1  is used to search Criteria_range1  for matching values. Once items in the range are found, their corresponding values in Sum_range  are added up.
Criteria1	Required. This is the criteria used to apply the filter on criteria1_range that selects the data subset. For example, criteria can be entered as 40, ">40", C6, "bolts", or "125".
Criteria_range2, criteria2, …	Optional. You can have additional range/criteria pairs up to a maximum of 127 pairs in total.

Note that, the Criteria_range argument must reference a range that has the same number of rows and columns as the Sum_range argument.

Example

In the following example, we want to sum up Sales totals using 2 criteria.

1. State name
2. Orders that are 40 or more (>=40)

Formula

The following formula is derived and come of the cell references have been converted to absolute references.

Tip : To convert a cell reference to an absolute reference in the formula bar, you can enter the dollar signs manually or click on the reference within the formula (i.e. D2:D12) and press the F4 key. This ensures that the reference will not change when the formula is copied to other cells. The difference between relative and absolute references is covered in my Excel 2016 Basics book.

=SUMIFS($D$2:$D$12,$B$2:$B$12,F2,$C$2:$C$12,G2)

Formula explanation

=SUMIFS($D$2:$D$12,$B$2:$B$12,F2,$C$2:$C$12,G2)

• The Sum_range argument references the Sales column D2:D12 (an absolute reference has been used here - $D$2:$D$12 ). 
• The Criteria_range1 is B2:B12 (an absolute reference has also been used here - $B$2:$B$12 ) 
• The Criteria1 argument is F2 . This is a reference to the States we want to use as our criteria. A cell reference has been used for this argument to make it easier to change. This has been left as a relative reference because we want it to change relatively as we copy the formula to other cells. 
• The Criteria_range2 is $C$2:$C$12 (in absolute reference form). 
• The Criteria2 argument is G2 (>=40). A cell reference has been used for this argument to make it easier to change. 

To sum up the Total Sales for the matching orders for each state, we enter the formula in cell H2 and then copy it down the column.