How to Sum If Cell Contains a Text in Excel (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to sum up numerical values based on the corresponding cells that contain text within them. So, it is quite a common scenario while analyzing data from a data table in Excel. So, in this article, we are going to teach you six easy ways, to sum up, numerical values in Excel if their corresponding cell contains specific text.

We will be using a sample product price list as a dataset to demonstrate all the methods throughout the article. Let’s have a sneak peek of it:

sum if cell contains text dataset

So, without having any further discussion, let’s dive straight into all the methods one by one.

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

In the spreadsheet, we have a product price list with categories. So, in this section, we will try to calculate the total price of the products under the Wafer category using the SUMIF function. Here are the steps to follow:

🔗 Steps:

First of all, select cell C15 to store the result of the SUMIF function.

Then, type the formula within the cell.

=SUMIF(B5:B12,"*Wafer*", E5:E12)

After that press the ENTER button.

Sum If Cell Contains Specific Text in Excel Using SUMIF Function

Formula Breakdown:

📌 Syntax: SUMIF(range, criteria, [sum_range])

  • Firstly, B5:B12 the range where the SUMIF function will look for the word “Wafer”.
  • Secondly, “*Wafer*” the search keyword.
  • Thirdly, E5: E12 the sum range.
  • Finally, =SUMIF(B5:B12,”*Wafer*”, E5:E12) returns the total price of the products under the “Wafer” category.

2. Applying Excel SUMIFS Function to Add Up Data If Cell Contains a Specific Text

Here, we will use the SUMIFS function to compute the total price of the products under the Wafer category.

🔗 Steps:

First of all, select cell C15 to store the result of the SUMIFS function.

Then, type the formula within the cell.

=SUMIFS(E5:E12,B5:B12,"*Wafer*")

After that press the ENTER button.

Add up If Cell Contains Text Using SUMIFS Function in Excel

Formula Breakdown:

📌 Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

  • At first, E5: E12 the sum range.
  • Then, B5:B12 the range where the SUMIFS function will look for the word “Wafer”.
  • Furthermore, “*Wafer*” the search keyword.
  • At last, =SUMIFS(E5:E12, B5:B12,”*Wafer*”) returns the total price of the products under the “Wafer” category.

3. Applying SUMIF Function to Sum If Cell Contains Text in Another Cell in Excel 

For our convenience and clarity, we may put search keywords in a separate cell. So, to handle those situations, you will learn the ways to perform the sum operation if the cell contains the text by the steps below.

🔗 Steps:

First of all, select cell C15 to store the result of the SUMIF function.

Then, type the formula within the cell.

=SUMIF(B5:B12,"*"&C14&"*",E5:E12)

After that press the ENTER button.

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

Formula Breakdown:

📌 Syntax: SUMIF(range, criteria, [sum_range])

  • In the beginning, B5:B12 the range where the SUMIF function will look for the word “Wafer”.
  • Again, “*”&C14&”*” refers to the address of the cell that contains the search keyword “Wafer”.
  • E5: E12 the sum range.
  • In the end, =SUMIF(B5:B12,”*”&C14&”*”,E5:E12) returns the total price of the products under the “Wafer” category.

4. Adding Up If Cell Contains Text in Another Cell Using SUMIFS Function

You can use the SUMIFS function to add up cells that contain text but in another cell. Follow the steps below to learn:

🔗 Steps:

First of all, select cell C15 to store the result of the SUMIF function.

  Type the formula

=SUMIFS(E5:E12,B5:B12,"*"&C14&"*")
within the cell.

After that press the ENTER button.

Add up If Cell Contains Text in Another Cell Using SUMIFS Function in Excel

Formula Breakdown:

📌 Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

  • E5: E12 the sum range.
  • B5:B12 the range where the SUMIFS function will look for the word “Wafer”.
  • “”*”&C14&”*”” refers to the address of the cell that contains the search keyword “Wafer”.
  • =SUMIFS(E5:E12,B5:B12,”*”&C14&”*”) returns the total price of the products under the “Wafer” category.

Read More: How to Sum If Cell Contains Text in Another Cell in Excel


5. Calculating the Total Price Based on Multiple Text Type (AND Criteria)

Criteria can be applicable for a single column as well as for multiple columns. In this section, we will learn formulas for both cases.

5.1 Summing If Cell Contains a Text Within a Single Column in Excel

This time, we will try to calculate the total price of the product under the Biscuit and Candies category. Follow the steps:

🔗 Steps:

First of all, select cell C15 to store the total price.

Then, type the formula

=SUM(SUMIF(B5:B12, {"Biscuit","Candies"},E5:E12))
within the cell.

Finally press the ENTER button.

Formula Breakdown:

📌 Syntax of the SUM function: SUM(number1,[number2],…)

📌 Syntax of the SUMIF function: SUMIF(range, criteria, [sum_range])

  • At first, B5:B12 the range where the SUMIF function will look for the word “Wafer”.
  • Then, “Biscuit”,”Candies” the search keywords.
  • Again, E5: E12 the sum range.
  • At last, =SUM(SUMIF(B5:B12, {“Biscuit”,”Candies”},E5:E12)) returns the total price of the products under the Biscuit and Candies category.

5.2 Summing If Cell Contains Text Within Multiple Columns in Excel

Now we will try to calculate the total price of the products under the “Pasta” category and have the word “Ravioli” in their product name. Follow the steps below to see how it works:

🔗 Steps:

First of all, select cell C15 to store the total price.

Then, type the formula

=SUMIFS(E5:E12,B5:B12,"Pasta",C5:C12,"Ravioli")
within the cell.

After that press the ENTER button.

Formula Breakdown:

📌 Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

  • E5: E12 the sum range.
  • Then, B5:B12 the range where the SUMIFS function will look for the word “Pasta”.
  • Next, “Pasta”,”Ravioli” the search keywords.
  • Again, C5:C12 the range where the SUMIFS function will look for the word “Ravioli”.
  • At last, =SUMIFS(E5:E12,B5:B12,”Pasta”,C5:C12,”Ravioli”) returns the total price of the products under the “Pasta” category and have “Ravioli” in the product name.

6. Calculating the Sum Value If the Cell Contains No Text in Excel

This time, we will calculate the total price for the products whose categories are missing. Follow the below steps:

🔗 Steps:

First of all, select cell C15 to store the result of the SUMIF function.

Then, type the formula

=SUMIF(B5:B12, "", E5:E12)
within the cell.

After that press the ENTER button.

Formula Breakdown:

📌 Syntax: SUMIF(range, criteria, [sum_range])

  • First, B5:B12 the range where the SUMIF function will look for the missing category.
  • Second, “” specifies blank cell.
  • Third, E5: E12 the sum range.
  • Finally, =SUMIF(B5:B12, “”, E5:E12) returns the total price of the products whose categories are missing.

Read More: How to Sum Only Numbers and Ignore Text in Same Cell in Excel


Things to Remember

  • Be careful about the syntax of the functions.
  • Insert the data ranges carefully into the formulas.

Download Practice Workbook

You are recommended to download the Excel file and practice along with it.


Conclusion

In conclusion, we have illustrated six different methods, to sum up, numerical values if the cell contains text in them. Again, you are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.


Excel Sum If Cell Contains Text: Knowledge Hub


<< Go Back to 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.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo