Excel SUMIFS Function
The Excel SUMIFS function returns the sum of all numbers in a specified range based on multiple criteria
Example: Excel SUMIFS Function
|
=SUMIFS(E9:E14,B9:B14,2017,D9:D14,"Bread")
|
Result in cell G9 (10,970) - returns the sum of all numbers in range (E9:E14) that are associated with year 2017 and Bread from ranges (B9:B14) and (D9:D14), respectively.
|
|
=SUMIFS(E9:E14,B9:B14,C5,D9:D14,C6)
|
Result in cell G9 (10,970) - returns the sum of all numbers in range (E9:E14) that are associated with the value in cells (C5) and (C6) from ranges (B9:B14) and (D9:D14), respectively.
|
METHOD 3. Excel SUMIFS function using the Excel built-in function library with hardcoded values
EXCEL
| =SUMIFS(E9:E14,B9:B14,2017,D9:D14,"Bread") Note: in this example we are summing the numbers in range (E9:E14) that are associated with year 2017 and Bread from ranges (B9:B14) and (D9:D14), respectively. |
 |
METHOD 4. Excel SUMIFS function using the Excel built-in function library with links
EXCEL
| =SUMIFS(E9:E14,B9:B14,C5,D9:D14,C6) Note: in this example we are summing the numbers in range (E9:E14) that are associated with the value in cells (C5) and (C6) from ranges (B9:B14) and (D9:D14), respectively. |
 |
Dim ws As Worksheet
ws.Range("G9") = Application.WorksheetFunction.SumIfs(ws.Range("E9:E14"), ws.Range("B9:B14"), 2017, ws.Range("D9:D14"), "Bread")
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named SUMIFS.
Sum Range: Ensure that the data you want sum is captured in range ("E9:E14").
Range: Ensure that the corresponding ranges (range1 and range2) to the sum range are captured in ranges ("B9:B14") and ("D9:D14"), respectively.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("G9") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Sum Range: Select the range that you want to sum by changing the range ("E9:E14") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding ranges (range1 and range2) to the sum range by changing the ranges ("B9:B14") and ("D9:D14"), respectively, to any range in the worksheet, that doesn't conflict with the formula.
Dim ws As Worksheet
ws.Range("G9") = Application.WorksheetFunction.SumIfs(ws.Range("E9:E14"), ws.Range("B9:B14"), ws.Range("C5"), ws.Range("D9:D14"), ws.Range("C6"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named SUMIFS.
Sum Range: Ensure that the data you want sum is captured in range ("E9:E14").
Range: Ensure that the corresponding ranges (range1 and range2) to the sum range are captured in ranges ("B9:B14") and ("D9:D14"), respectively.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("G9") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Sum Range: Select the range that you want to sum by changing the range ("E9:E14") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding ranges (range1 and range2) to the sum range by changing the ranges ("B9:B14") and ("D9:D14"), respectively, to any range in the worksheet, that doesn't conflict with the formula.
The Excel SUMIFS function returns the sum of all numbers in a specified range based on multiple criteria.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
sum_range: (Required) The range of cells you want to sum from.
criteria_range1: (Required) The range of cells that you want to test criteria1 against.
criteria1: (Required) The criteria that is used to determine which of the cells, from range1, should be summed.
criteria_range2: (Optional) The range of cells that you want to test criteria2 against.
criteria2: (Optional) The criteria that is used to determine which of the cells, from range2, should be summed.
ADDITIONAL NOTES
Note 1: The SUMIFS function allows the use of logical elements (>,<,<>,=).
Note 2: The SUMIFS function allows the use of wildcards:
* searches to find a match for the sequence of characters.
? searches to find a match to any single character.