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

Get FREE Advanced Excel Exercises with Solutions!

We often need to add numerical numbers depending on the cells that contain specific text. When using Excel to analyze data from a data table, this is a very typical occurrence. In this article, we will learn some quick methods to sum if cell contains text in another cell.

This article will guide you to sum up if a cell contains text in another cell with 5 quick methods. We have used some excellent examples with explanations for a better understanding of the methods. Here, we have used a dataset (B4:E9) to describe the methods that contain the Order No., Item Names, Quantity and Sales of some products. Let’s see the methods below.

5 Quick Methods to Sum If Cell Contains Text in Another Cell


1. Sum If Cell Contains Text in Another Cell Using SUMIF Function in Excel

We can sum the values in a range that satisfies a set of criteria by using the SUMIF function. In the dataset (B4:E9) below, we have some Item Names with Sales. Using the SUMIF function, we will now attempt to get the Total Sales of the Dark Chocolate in this section. The steps to do so are below.

Steps:

  • First of all, go to cell C12 where we want to store the result.
  • Next, type the formula in the cell (C12):
=SUMIF(C5:C9,"*"&C11&"*",E5:E9)

Sum If Cell Contains Text in Another Cell Using SUMIF Function in Excel

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

  • After that, press the Enter key to get the Total Sales.


2. Apply Excel SUMIFS Function to Add up If Cell Contains Text in Another Cell

SUMIFS is an Excel function that adds cells that satisfy multiple criteria. Here, we will use the SUMIFS function to get the Total Sales of Dark Chocolate. Let’s see the steps below.

Steps:

  • In the beginning, click on cell C12.
  • Secondly, type the formula below in the cell (C12):
=SUMIFS(E5:E9,C5:C9,"*"&C11&"*")

Apply Excel SUMIFS Function to Add up If Cell Contains Text in Another Cell

In this formula, the E5:E9 and C5:C9 ranges indicate the Sales and Item Names successively. Here, C11 contains the search keyword (Dark Chocolate).

  • After pressing the Enter button, we will get our desired result. We can see the final output, in cell C12 of the screenshot below.


3. Sum If Cell Contains Specific Text in Excel Using Wildcard in SUMIF Function

Excel’s wildcard characters are unique symbols that can be used in formulas to replace other characters. In this method, we will use the asterisk (*) symbol in the SUMIF function to sum up the Sales of Dark Chocolate in the dataset (B4:E9) below. To do so, follow the steps below.

Steps:

  • Firstly, select cell C12 to keep the value of Total Sales.
  • Now, type the formula below in the cell:
=SUMIF(C5:C9,"*DARK*",E5:E9)

Sum If Cell Contains Specific Text in Excel Using Wildcard in SUMIF Function

In the formula, the range C5:C9 indicates the Item Names and E5:E9 indicates the Sales of the items. Here, ‘*Dark*’ is the search keyword.

  • Lastly, press Enter to get the final output.


4. Insert Excel SUMIFS Function with Wildcard to Add Cell Containing Specific Text

Here, we will use the SUMIFS function with wildcard (*) to calculate the Total Sales of Dark Chocolate. Let’s follow the steps below to do so.

Steps:

  • To begin with, select cell C12.
  • Subsequently, type the formula below in this cell:
=SUMIFS(E5:E9,C5:C9,"*Dark*")

Insert Excel SUMIFS Function with Wildcard to Add Cell Containing Specific Text

Here, E5:E9 indicates the sum range, C5:C9 denotes the range where the SUMIFS function will search for the keyword ‘Dark’ and ‘*Dark*’ refers to the search keyword.

  • Finally, press the Enter key to get the result.


5. Calculate Total Price by Combining SUM Function with SUMIF & SUMIFS Function

5.1. Within One Column

In this method, we will attempt to calculate the Total Sales of White & Milk Chocolate by combining the SUM function with the SUMIF function within a single column. Let’s see the steps below.

Steps:

  • First, to store the result of Total Sales, go to cell C12.
  • Then, type the formula:
=SUM(SUMIF(C5:C9,{"White Chocolate","Milk Chocolate"},E5:E9))
  • In the end, press the Enter key.

Calculate Total Price by Combining SUM Function with SUMIF & SUMIFS Function

Here, C5:C9 is the range where the SUMIF function will search for the keywords, White Chocolate and Milk Chocolate are the search keywords. E5:E9 is the sum range. Moreover, SUM(SUMIF(C5:C9,{“White Chocolate”,”Milk Chocolate”},E5:E9)) will return the Total Sales of the items White Chocolate and Milk Chocolate.

Read more: How to Sum Names in Excel


5.2. Among Several Columns

This time, we will use the SUMIFS function to calculate the Total Sales of Order No. 4 which is Milk Chocolate. However, we will do this calculation among several columns. The steps to do so are below.

Steps:

  • In the first place, click on cell C12.
  • Therefore, type the formula below in the cell:
=SUMIFS(E5:E9,B5:B9,"3",C5:C9,"Milk Chocolate")
  • Lastly, press the Enter button to find the desired output.

Calculate Total Price by Combining SUM Function with SUMIF & SUMIFS Function

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 the word Milk Chocolate.


Things to Keep in Mind

  • We can use the SUMIF function in Excel, for situations where we want to add numbers in a range of cells depending on text criteria.
  • There is no case sensitivity in the SUMIF function.

Download Practice Workbook

Download the practice workbook from here.


Conclusion

I hope the above methods will be helpful for you to sum if a cell contains text in another cell in Excel. Download the practice workbook and give it a try.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sagufta Tarannum
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo