Sum values if ends with

To sum values if corresponding cells end with a specific value you can apply an Excel or a VBA method using the Excel SUMIF function

Example: Sum values if ends with

Sum values if ends with

METHOD 1. Sum values if ends with a specific value

EXCEL

=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) ends with "es", which is specified in cell C5. The asterisk (*) represents one or more characters.

METHOD 1. Sum values if ends with a specific value using VBA

VBA

Sub Sum_values_if_ends_with_a_specific_value()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum the values in range (C8:C14) when the values in range (B8:B14) ends with "es", which is specified in cell (C5)
ws.Range("E8") = Application.WorksheetFunction.SumIf(ws.Range("B8:B14"), "*" & ws.Range("C5"), ws.Range("C8:C14"))

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.
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 when the values in range (B8:B14) ends with 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") ends with 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.

Explanation about the formula used to sum values if ends with a specific value

EXPLANATION

EXPLANATION
To sum values if corresponding cells end with a specific value you can apply an Excel or a VBA method. The formula used to sum values if corresponding cells end with 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) ends with "es", which is the value in cell C5. This is achieved through the use of the Excel SUMIF function and the asterisk (*) wildcard.
FORMULA
=SUMIF(range, *value, sum_range)
ARGUMENTS
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 the "range" end with the specified 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, sum_range)).