Sum values if cells are equal to
To sum values if corresponding cells are equal to a specified value you can apply the Excel SUMIF function
Example: Sum values if cells are equal to
|
=SUMIF(C8:C13,C5,D8:D13)
|
The formula uses the Excel SUMIF function to sum the numbers that are assigned to a specific value in cell C5, which in this example is "Bread", in the corresponding cells in range (C8:C13). Therefore, it sums the values in cells D8 and D11 which amounts to 1,580.
|
Sub Sum_values_if_cells_are_equal_to()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum values if cells are equal to
ws.Range("F8") = Application.WorksheetFunction.SumIf(ws.Range("C8:C13"), ws.Range("C5"), ws.Range("D8:D13"))
ws.Range("F8") = Application.WorksheetFunction.SumIf(ws.Range("C8:C13"), ws.Range("C5"), ws.Range("D8:D13"))
End Sub
OBJECTS
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.
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.
PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
Sum Range: Ensure that the data you want sum is captured in range ("D8:D13").
Range: Ensure that the corresponding range to the sum range is captured in range ("C8:C13").
Worksheet Name: Have a worksheet named Analyst.
Sum Range: Ensure that the data you want sum is captured in range ("D8:D13").
Range: Ensure that the corresponding range to the sum range is captured in range ("C8:C13").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the range that you want to sum by changing the range ("D8:D13") 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 ("C8:C13") to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the range that you want to sum by changing the range ("D8:D13") 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 ("C8:C13") to any range in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To sum values if corresponding cells are equal to a specified value you can apply the Excel SUMIF function. In this example the SUMIF function is used to sum the numbers in range (D8:D13) that are assigned to a specific value in cell C5, which is "Bread", in the corresponding cells in range (C8:C13).
To sum values if corresponding cells are equal to a specified value you can apply the Excel SUMIF function. In this example the SUMIF function is used to sum the numbers in range (D8:D13) that are assigned to a specific value in cell C5, which is "Bread", in the corresponding cells in range (C8:C13).
FORMULA
=SUMIF(range, value, sum_range)
=SUMIF(range, value, sum_range)
ARGUMENTS
range: The range of cells you want to test the value against.
value: The value that is used to determine which of the cells, from the specified range, should be summed. In this example we have specified this value to be "Bread".
sum_range: The range of cells you want to sum from.