Sum values if less than
To sum values that are less than a specific value you can apply an Excel or a VBA method using the Excel SUMIF function
Example: Sum values if less than
|
=SUMIF(C8:C14,"<"&C5)
|
The formula uses the Excel SUMIF function to sum the number of cells in range (C8:C14) that have a value less than the value in cell C5.
|
Sub Sum_values_if_less_than()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum the values that have a value less than the value in cell (C5)
ws.Range("E8") = Application.WorksheetFunction.SumIf(ws.Range("C8:C14"), "<" & ws.Range("C5"))
ws.Range("E8") = Application.WorksheetFunction.SumIf(ws.Range("C8:C14"), "<" & ws.Range("C5"))
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 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 if the cell's value, in the specified range, is less than this value.
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 if the cell's value, in the specified range, is less than this value.
ADJUSTABLE PARAMETERS
Specific Value: Select the specific value, by changing the value in cell ("C5"), that you want to sum if a cell's value, from a specified range, is less than this 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 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.
EXPLANATION
To sum values that are less than a specific value you can apply an Excel or a VBA method. The formula used to sum values that are less than 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) that contain a value less than 500, which is the value in cell C5. This is achieved through the use of the Excel SUMIF function.
To sum values that are less than a specific value you can apply an Excel or a VBA method. The formula used to sum values that are less than 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) that contain a value less than 500, which is the value in cell C5. This is achieved through the use of the Excel SUMIF function.
FORMULA
=SUMIF(range, "< value")
=SUMIF(range, "< value")
ARGUMENTS
range: The range of cells you want to test the value against and sum from.
value: The value that is used to determine which of the cells should be summed if the cells' value, from a specified range, are less than this value. If the value is referencing to a cell, as per the example in this tutorial, you need to insert the & sign before the cell reference (e.g. SUMIF(range, "<"&value)).
range: The range of cells you want to test the value against and sum from.
value: The value that is used to determine which of the cells should be summed if the cells' value, from a specified range, are less than this value. If the value is referencing to a cell, as per the example in this tutorial, you need to insert the & sign before the cell reference (e.g. SUMIF(range, "<"&value)).