< Go Back

Sum if cell contains text in another cell

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.  

Formula

Generic formula syntax for SUMIF or SUMIFS function is:

=SUMIF( range,"*"&text_value&"*", sum_range)

OR

=SUMIFS(sum_range, range,"*"&text_value&"*")

Here,

  • 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.

Explanation

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 (*):

"*"&F6&"*"

When stand out to evaluate this argument inside the SUMIF function, it’ll “see” like this: “*shirt*”.

Example

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:

=SUMIF(C5:C9,"*"&F6&"*",D5:D9)

OR

=SUMIF(C5:C9,"*"&"shirt"&"*",D5:D9)

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.

Notes

  • SUMIF or SUMIFS function is not case-sensitive.
  • Notice that the sum_range argument is always supplied first in SUMIFS function.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar