In this article, we will learn how to sum if values only if text ends with a certain characters.
Sometimes we need to find the sum of values with matching end value. SUMIF function can sum up the array where the criteria range contains text that ends with a particular sub-string. We will use wildcards in the Excel SUMIF function to pull this off.
Excel lets you use Wildcard characters for catching strings and perform functions on it. Here we just need to use one type of wildcard as an example.
We will construct a formula out of it. First, the text value that needs to be matched is combined with wildcard to search values matching the same. Then the matched values will return values corresponding to the same. Last but not least, the SUM operator will return the sum of the values.
Use the formula
Let's get this through with an example.
Here we have a list of received orders and their respective values.
Here we need to get the sum if the region is either East or West. Since both string contains "st" at the end, we can use the * wildcard instead of SUMIF with or logic.
We will use the formula to get the total amount.
Explanation for the formula:
D2:D15 : Sum_range where values gets sum
B2:B15 : range where criteria *value (criteria) is matched.
"*st" : value criteria, matches East or West.
Here the argument arrays to the function is given as cell reference.
Use the formula as stated above to calculate the total price and click Enter.
As you can see the formula returns $916.63 , the Total price for the yellow rows marked.
You can also use the SUMIFS function formula to get the same result.
SUMIFS function returns the sum of the range having multiple criteria.
Syntax of the formula:
These both function can handle wildcards. And hence the result.
Hope you understood how to get the SUM values if matches end value in Excel. Explore more articles on Excel SUMIFS function here. Please feel free to state your query or feedback for the above article.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.