Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook

Download the practice workbook from here.


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

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.

Read More: How to Use SUMIF with Text in Excel (9 Easy Ways)


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.

Read More: How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)


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.

Read More: How to Sum If Cell Contains Specific Text in Excel (6 Ways)


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.

Read More: How to Assign Value to Text and Sum in Excel (2 Easy Methods)


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.


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.

Read More: How to Sum Text Values Like Numbers in Excel (3 Methods)


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.

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. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.


Related Articles

Sagufta Tarannum

Sagufta Tarannum

Hi, I am Sagufta. I have completed my graduation in Civil Engineering from Bangladesh University of Engineering and Technology. I am very much interested about research and innovation in the field of Civil Engineering.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo