Adding values based on text conditions is an incredibly useful skill. For example, you can add values based on a cell containing a partial string, such as cells containing a particular product code. In this tutorial, you’ll learn how to use the Excel SUMIF() function to add values containing a partial text, starting with a given text, and ending with a given text. We’ll walk through hands-on examples, showing you how to maximize the SUMIF() function when working with text data.
By the end of this tutorial, you’ll have learned the following:
- How to use Excel SUMIF() with partial text matches
- How to use Excel SUMIF() for cells starting with a particular text strings
- How to use Excel SUMIF() for cells ending with a particular text strings
Excel SUMIF() with Containing Partial Text Matches
The Excel SUMIF() function is used to add values based on specific criteria, such as cells containing text.
Let’s take a look at the Excel SUMIF() function and its arguments to better understand how to use it:
=SUMIF(range, criteria, [sum_range])As you can see from the code block above, the function takes three arguments. Let’s take a closer look at them in the table below:
| Argument | Description | Required? |
range= | The range of cells to apply the condition to | Yes |
criteria= | The condition to apply, including any of the logical operators that may be required | Yes |
sum_range= | The range of cells to add up. If empty, Excel adds up the values in the range= argument. | No |
The arguments of the Excel SUMIF() function
Being able to add values that contain partial text can be done by using the wildcard operator *. Excel uses two wildcard operators: ? which checks for only a single character and * which checks for any number of characters.
In order to use the Excel SUMIF() function to add cells containing partial matches, we can use the following formula:
=SUMIF(criteria_range, "*"&text&"*", sum_range)Let’s see what this looks like by taking a look at a practical example:
In the example above, we have our text in range B3:B13 and the values we want to add up in range C3:C13. Specifically, we’re looking for cells that contain the string "o", which we’re storing in cell F2. We can use the following formula to add cells that contain the partial text:
=SUMIF(B3:B13, "*"&F2&"*",C3:C13)Notice that we’re wrapping the wildcard operator in double-quotes. This is necessary so that Excel doesn’t throw an error. If we were hard coding the condition (rather than using a cell reference), we could use "*o*". However, this doesn’t allow our formula to be dynamic, such as if we wanted to modify the text in cell F2.
In the following section, you’ll learn to how to add values based on cells starting with certain text.
Excel SUMIF() for Cells Starting with Text
In this section, you’ll learn how to use Excel to add values that start with a particular text. This can be useful if you want to add cells that follow a specific pattern, such as a product code. In order to do this, we can use the wildcard operator at the end of our condition. This allows you to instruct Excel to check for a particular substring at the beginning of a text, while allowing for any text to follow.
Let’s take a look at what the formula for this looks like:
=SUMIF(criteria_range, "text*", sum_range)Let’s now take a look at an example of how to do this in Excel:
In the example above, we’re adding up values that begin with the letter "S". Because the Excel SUMIF() function is not case sensitive, the function will add values beginning with a lowercase or uppercase S.
In the following section, you’ll learn how to use Excel to add cells that end with a particular text.
Excel SUMIF() for Cells Ending with Text
In this section, you’ll learn how to use the Excel SUMIF() function to add values for cells that end with a particular substring. For example, if you’re working with regional data, you may be wanting to add all cells that end in “West”. This could include summing values that are “Northwest”, “Southwest”, and “West”. Because the SUMIF() function is case insensitive, we don’t need to specify the casing for function.
Let’s take a look at how we can use Excel to add values ending with a particular substring:
In the example above, we’re using the following formula:
=SUMIF(B3:B13, "*"&F2, C3:C13)The formula uses the wildcard operator at the beginning of our condition. This includes only cells that end with a particular substring, which in this case is “th”.
Conclusion
In this tutorial, you learned how to use the Excel SUMIF() function to add values that contain a particular substring of text. You learned how the function can be used in combination with wildcard operators to sum values conditionally.
You first learned how to search for a particular substring by including wildcard operators on either side of the substring. Then, you learned how to add values that either begin or end with a substring. This was done by either prefixing or suffixing the wildcard to add cells conditionally. By walking through practical, hands-on examples, you learned how to use wildcard operators in order to make summing values easier.
Additional Resources
To learn more about related topics, check out the resources below: