If you need to sum the numeric values of cells where the corresponding cells contain a certain text value, then it becomes very tedious work to do so. But Excel has a special function to perform this activity quickly and very easily. You need to use Excel SUMIF or SUMIFS function to do so. This article will give an overview of how to sum if the cell contains text in another cell in your dataset.
Generic formula syntax for SUMIF or SUMIFS function is:
=SUMIF( range,"*"&text_value&"*", sum_range)
- Range argument consists of cells range that contains text sting and where you need to test the presence of your specific text value or criterion value.
- Criteria argument contains specific text values along with concatenating operator (&) and wildcard character (*) to search if range argument’s cells contain that text value.
- Sum_range argument contains the numeric values that need to be sum.
In a situation where we want to match the character text in a cell reference, we won’t write the criteria like “*F6*” because it will match solely the literal text “F6”. Instead, we need to use the concatenating operator (&) to affix a reference to F6 to asterisks (*):
When stand out to evaluate this argument inside the SUMIF function, it’ll “see” like this: “*shirt*”.
Suppose you have a data set of orders where products are listed in cells as a string in column C and the order value is listed in column D. And you want to sum the amounts of those orders where the specific product is in listed in product description field or column C.
As per Figure 1 you need to sum the orders’ amount from column D where text value “shirt” is found in column C, using SUMIF function as below:
Figure 1. Sum cell contains text in another cell
In the Figure 2 shown below, the formula in cell G6 is:
=SUMIF (C5:C9,"*"&F6&"*", D5:D9)
Figure 2. Example 1
In the example below, G6 contains this formula:
=SUMIFS (D5:D11, C5:C11,"*"&F6&"*")
Figure 3. Example 2
This formula sums the quantity in column D when a value in column C contains the character value in cell F6.
- SUMIF or SUMIFS function is not case-sensitive.
- Notice that the sum_range argument is always supplied first in SUMIFS function.