Excel SUMIF Function
The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria
Example: Excel SUMIF Function
|
=SUMIF(D5:D10,">500")
|
Result in cell D13 (3,972) - returns the sum of all numbers in range (D5:D10) that have a value greater than 500.
|
|
=SUMIF(C5:C10,"Bread",D5:D10)
|
Result in cell D14 (1,580) - returns the sum of all numbers in range (D5:D10) that is associated with Bread from range (C5:C10).
|
|
=SUMIF(D5:D10,">"&C13)
|
Result in cell D13 (3,972) - returns the sum of all numbers in range (D5:D10) that have a value greater than the value in cell (C13).
|
|
=SUMIF(C5:C10,C14,D5:D10)
|
Result in cell D14 (1,580) - returns the sum of all numbers in range (D5:D10) that are associated with the value in cell (C14) from range (C5:C10).
|
METHOD 3. Excel SUMIF function using the Excel built-in function library with hardcoded values
EXCEL
| =SUMIF(C5:C10,"Bread",D5:D10) Note: in this example we are summing all of the numbers in range (D5:D10) that are associated with Bread from range (C5:C10). |
 |
METHOD 4. Excel SUMIF function using the Excel built-in function library with links
EXCEL
| =SUMIF(C5:C10,C14,D5:D10) Note: in this example we are summing all of the numbers in range (D5:D10) that are associated with the value in cell (C14) from range (C5:C10). |
 |
Dim ws As Worksheet
ws.Range("D13") = Application.WorksheetFunction.SumIf(ws.Range("D5:D10"), ">500")
ws.Range("D14") = Application.WorksheetFunction.SumIf(ws.Range("C5:C10"), "Bread", ws.Range("D5:D10"))
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 SUMIF.
Sum Range: Ensure that the data you want sum is captured in range ("D5:D10").
Range: Ensure that the corresponding range to the sum range is captured in range ("C5:C10").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D13") and ("D14") 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 ("D5:D10") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding range to the sum range by changing the range ("C5:C10") to any range in the worksheet, that doesn't conflict with the formula.
Dim ws As Worksheet
ws.Range("D13") = Application.WorksheetFunction.SumIf(ws.Range("D5:D10"), ">" & ws.Range("C13"))
ws.Range("D14") = Application.WorksheetFunction.SumIf(ws.Range("C5:C10"), ws.Range("C14"), ws.Range("D5:D10"))
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 SUMIF.
Sum Range: Ensure that the data you want sum is captured in range ("D5:D10").
Range: Ensure that the corresponding range to the sum range is captured in range ("C5:C10").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D13") and ("D14") 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 ("D5:D10") to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the corresponding range to the sum range by changing the range ("C5:C10") to any range in the worksheet, that doesn't conflict with the formula.
The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria.
=SUMIF(range, criteria, [sum_range])
range: (Required) The range of cells you want to test the criteria against.
criteria: (Required) The criteria that is used to determine which of the cells, from the specified range, should be summed.
sum_range: (Optional) The range of cells you want to sum from.
ADDITIONAL NOTES
Note 1: The SUMIF function allows the use of logical elements (>,<,<>,=).
Note 2: The SUMIF 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.