Sum values if contains specific value
To sum values if corresponding cells contain a specific value you can apply an Excel or a VBA method using the Excel SUMIF function
Example: Sum values if contains specific value
|
=SUMIF(B8:B14,"*"&C5&"*",C8:C14)
|
The formula uses the Excel SUMIF function and the asterisk (*) to sum the values in range (C8:C14) when the values in range (B8:B14) contain "re", which is specified in cell C5. The asterisk (*) represents one or more characters.
|
Dim ws As Worksheet
ws.Range("E8") = Application.WorksheetFunction.SumIf(ws.Range("B8:B14"), "*" & ws.Range("C5") & "*", ws.Range("C8:C14"))
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 Analysis.
Data Range: Ensure that the data that you want to sum from is captured in range ("C8:C14") in the Analysis worksheet.
Specific Value: Input the specific value in cell ("C5") in the Analysis worksheet that you want to sum when the values in range ("B8:B14") contain that specific value.
ADJUSTABLE PARAMETERS
Specific Value: Select the specific value, by changing the value in cell ("C5"), that you want to sum when the values in range ("B8:B14") contain that specific value. Alternatively, you can replace ws.Range("C5") in the VBA code with the specific value or a defined name that represents the specific value that you want to test for.
Date Range: Select the range that you want test the criteria against by changing range ("B8:B14") to any range in the worksheet that doesn't conflict with the formula.
Sum Range: Select the range that you want to sum from by changing range ("C8:C14") to any range in the worksheet that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("E8") to any cell in the worksheet, that doesn't conflict with the formula.
To sum values if corresponding cells contain a specific value you can apply an Excel or a VBA method. The formula used to sum values if corresponding cells contain a specific value is driven by an Excel SUMIF function.
In both the VBA and Excel examples the formula sums the values from a specified range (C8:C14) when the values in range (B8:B14) contain "re", which is the value in cell C5. This is achieved through the use of the Excel SUMIF function and the asterisk (*) wildcard.
=SUMIF(range, *value*, sum_range)
range: The range of cells you want to test the value against and sum from.
sum_range: The range of cells you want to sum from.
value: The value that is used to determine which of the cells should be summed from the "sum_range" when the values in "range" contain the value. If the value is referencing to a cell, as per the example in this tutorial, you need to insert the "*"& and &"*" signs before and after the cell reference (e.g. SUMIF(range, "*"&value&"*",sum_range)).