Sometimes we need to sum up numerical values based on the corresponding cells that **contain text** within them. This is quite a common scenario while analyzing data from a data table in Excel. 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.

## Download the Practice Workbook

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

## 6 Ways to Sum If Cell Contains Specific Text in Excel

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:

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

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

In the spreadsheet, we have a product price list with categories. Now 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

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

❸ After that press the **ENTER** button.

**␥** **Formula Breakdown:**

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

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

**Read More:** **Excel Sum If a Cell Contains Criteria (5 Examples)**

### 2. Add up If Cell Contains Text Using SUMIFS Function in Excel

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

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

❸ After that press the **ENTER** button.

**␥** **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**”.**“*Wafer*”**▶ the search keyword.**=SUMIFS(E5:E12, B5:B12,”*Wafer*”)**▶ returns the total price of the products under the “**Wafer**” category.

**Read More:** **How to Add Specific Cells in Excel (5 Simple Ways)**

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

For our convenience and clarity, we may put search keywords in a separate cell. For those situations to handle, 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

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

❸ After that press the **ENTER** button.

**␥** **Formula Breakdown:**

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

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

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

**Similar Readings**

**How to Sum Between Two Numbers Formula in Excel****All the Easy Ways to Add up (Sum) a column in Excel****How to Add Numbers in Excel (2 Easy Ways)****Sum Selected Cells in Excel (4 Easy Methods)****How to Sum Multiple Rows and Columns in Excel**

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

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.

❷ Then, **type** the formula

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

❸ After that press the **ENTER** button.

**␥** **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:** **Sum Cells in Excel: Continuous, Random, With Criteria, etc.**

### 5. Calculate the Total Price If Cell Contains Text with Multiple AND Criteria in Excel

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 Within a Single Column

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))`

❸ After that press the **ENTER** button.

**␥** **Formula Breakdown:**

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

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

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

**Read More:** **How to Sum a Column in Excel (6 Methods)**

#### 5.2 Within Multiple Columns

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")`

❸ After that press the **ENTER** button.

**␥** **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 “**Pasta**”.**“Pasta”,”Ravioli”**▶ the search keywords.**C5:C12**▶ the range where the**SUMIFS**function will look for the word “**Ravioli**”.**=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.

**Read More:** **How to Sum Columns in Excel (7 Methods)**

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

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)`

❸ After that press the **ENTER** button.

**␥** **Formula Breakdown:**

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

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

**Read More:** **How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)**

## Things to Remember

📌 Be careful about the **syntax** of the functions.

📌 **Insert** the data **ranges** carefully into the formulas.

## Conclusion

To wrap up, we have illustrated six different methods, to sum up, numerical values if the cell contains text in them. 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.