# How to Sum If Cell Contains Text in Another Cell in Excel

Here’s a dataset that contains the Order No., Item Names, Quantity and Sales of some products. We’ll use it to create sums depending on certain values in the dataset. There’s a lookup cell and a result cell below the set (see image).

## Method 1 – Sum If Cell Contains Text in Another Cell Using the SUMIF Function

We will get the Total Sales of Dark Chocolate.

Steps:

• Go to cell C12 and use the following formula.
`=SUMIF(C5:C9,"*"&C11&"*",E5:E9)`

The ranges C5:C9 and E5:E9 indicate the Item Names and Sales, respectively. C11 denotes the location of the cell containing the search keyword (Dark Chocolate).

• Press the Enter key to get the Total Sales.

## Method 2 – Apply the SUMIFS Function to Add up If Cell Contains Text in Another Cell

We’ll get the Total Sales of Dark Chocolate.

Steps:

• Insert the following formula in C12.
`=SUMIFS(E5:E9,C5:C9,"*"&C11&"*")`

• Hit Enter.

## Method 3 – Sum If Cell Contains Specific Text in Excel Using Wildcards in the SUMIF Function

We’ll sum up the Sales of all items that contains the word Dark in the dataset.

Steps:

• Insert the formula below in cell C12:
`=SUMIF(C5:C9,"*DARK*",E5:E9)`

Here, ‘*Dark*’ is the search keyword.

• Press Enter to get the final output.

## Method 4 – Insert the SUMIFS Function with Wildcard to Add Cells Containing Specific Text

We’ll sum up the sales of items that contain the word Dark somewhere in the name.

Steps:

• Select cell C12.
• Insert the formula below in the cell:
`=SUMIFS(E5:E9,C5:C9,"*Dark*")`

• Press the Enter key to get the result.

## Method 5 – Calculate the Total Price by Combining the SUM Function with SUMIF and SUMIFS Function

### Case 5.1 – Within One Column

Let’s calculate the Total Sales of White Chocolate and Milk Chocolate.

Steps:

• Go to cell C12.
• Insert the formula:
`=SUM(SUMIF(C5:C9,{"White Chocolate","Milk Chocolate"},E5:E9))`
• Press the Enter key.

The SUMIF function will search for the keywords White Chocolate and Milk Chocolate within the C5:C9 range. E5:E9 is the sum range. If a cell in the C5:C9 range contains one of the keywords, its respective cell in the E5:E9 range will get added to the result.

Read more: How to Sum Names in Excel

### Case 5.2 – Among Several Columns

We’ll calculate the Total Sales of Order No. 4 which is Milk Chocolate.

Steps:

• Click on cell C12.
• Use the formula below in the cell:
`=SUMIFS(E5:E9,B5:B9,"3",C5:C9,"Milk Chocolate")`
• Press the Enter button to find the desired output.

Here, E5:E9 is the sum range. B5:B9 is the range where the SUMIFS function will find the number 3. Moreover, C5:C9 refers to the range where the SUMIFS function will search for the words Milk Chocolate.

## Things to Keep in Mind

• There is no case sensitivity in the SUMIF function.

## Related Articles

<< Go Back to Excel Sum If Cell Contains Text | Excel SUMIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF