In this article, Iâ€™ll show you how to sum if a cell contains text in Excel. Youâ€™ll learn the methods to sum based on a cell containing any sort of text, along with if it **contains a specific text** with proper examples and illustrations.

**Table of Contents**hide

**Download Practice Workbook**

**6 Suitable Formulas to Sum If a Cell Contains Text in Excel**

Here weâ€™ve got a data set with the **Names** of some products, **Contact Addresses** of the customers who bought them, and the **Quantities** of a company called Jupyter Group.

Today our objective is to sum the cells that contain text from this data set.

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

You can use the **SUMIF function** of Excel to sum if a cell contains text in Excel.

For example, letâ€™s try to sum the quantities of the products whose customersâ€™ addresses are **Email IDs**, not **Telephone Numbers**.

That means, we need to sum the **Quantity** of a cell if its adjacent cell contains a text as the **Customer Address**.

**â§Ş**** How to Accomplish This?**

To execute this, you can enter an **Asterisk Symbol (*)** as the **criteria** within a **SUMIF function**, like the following formula:

`=SUMIF(C4:C13,"*",D4:D13)`

Look, here weâ€™ve got the total quantity of products with customers having text addresses.

It is 1558.

**â§Ş**** Explanation of the Formula**

- The
**SUMIF function**takes three arguments: a**range**, a**criteria**, and a**sum_range**. - Here the range is
**C4:C13 (Customer Address)**and the**criteria**is**â€ś*â€ť**.**â€ś*â€ť**holds**TRUE**for any text value. Therefore, the formula searches for all the text values in the range**C4:C13**. - When it finds a text value in the range
**C4:C13**, it sums the corresponding value from the**sum_range**,**D4:D13**(**Quantity**). - Thus
**SUMIF(C4:C13,â€ť*â€ť,D4:D13)**returns the sum of all the quantities from the range**D4:D13**where the corresponding address in the range**C4:C13**is a text address.

**Read more:** **How to Sum Cells with Text and Numbers in Excel**

**2. Use SUMIFS Function to Sum If a Cell Contains Text in Excel**

You can use the **SUMIFS function** instead of the **SUMIF function** to sum if a cell contains text in Excel.

**â§Ş**** How to Accomplish?**

The formula is almost similar. Here, the **SUMIFS **formula to sum the quantities with the text addresses will be:

`=SUMIFS(D4:D13,C4:C13,"*")`

Here, weâ€™ve again got the total quantity of products with customers having text addresses.

It is 1558 again.

**â§Ş**** Explanation of the Formula**

- The
**SUMIFS function**takes a**sum_range**and one or more pairs of**range**and**criteria.** - Here our
**sum_range**is**D4:D13**(**Quantity**). And we have used one pair of a**range**and**criteria**. - The range is
**C4:C13 (Contact Address)**, and the criteria is**â€ś*â€ť**. It searches for all the text values in the range**C4:C13**. - When it finds a text value in the range
**C4:C13**, it sums the corresponding value from the**sum_range****D4:D13**. - Thus
**SUMIFS(D4:D13,C4:C13,â€ť*â€ť)**returns the sum of all the quantities from the range**D4:D13**where the corresponding address in the range**C4:C13**is a text address.

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

**3. Combine SUM, IF, and ISTEXT Functions to Sum If a Cell Contains Text in Excel**

If you wish, you can use a combination of the **SUM function**, the **If function**, and the **ISTEXT function** to sum if a cell contains text in Excel.

**â§Ş**** How to Accomplish?**

Select any cell and enter this combined formula:

`=SUM(IF(ISTEXT(C4:C13),D4:D13,0))`

**[**Itâ€™s an **Array Formula**. So press **CTRL+SHIFT+ENTER** unless you are in **Office 365**.]

Look, weâ€™ve got the same total quantity of products with customers having text addresses, 1558.

**â§Ş**** Explanation of the Formula**

**ISTEXT(C4:C13)**checks each value in the range**C4:C13**and returns a**TRUE**if itâ€™s a text value. Otherwise, it returns a**FALSE**.- Now the formula becomes
**SUM(IF({TRUE,TRUE,FALSE,â€¦,FALSE},D4:D13,0)).** **IF({TRUE,TRUE,FALSE,â€¦,FALSE},D4:D13,0)**returns the corresponding value from the range**D4:D13**for each**TRUE**. And for each**FALSE**, it returns**0**.- Therefore the formula becomes
**SUM(D4,D5,0,D7,0,0,0,D11,D12,0).** - Now the
**SUM function**returns the sum of the corresponding values from the range**D4:D13**.

**Similar Readings:**

**Sum Cells in Excel: Continuous, Random, With Criteria, etc.****Sum Multiple Rows and Columns in Excel****How to Use VLOOKUP with SUM Function in Excel (6 Methods)**

**4. Use SUMIF Function to Sum If a Cell Contains a Specific Text in Excel (Case-Insensitive Match)**

Up till now, we have summed all the cells that contain text values.

Now we will try a bit different thing. We will sum the cells that contain text values with a specific text.

For example, letâ€™s try to sum the quantity of all the red products.

That means, we have to sum any cell if it contains the text **â€średâ€ť** in it.

**â§Ş**** How to Accomplish This?**

We can also accomplish this using the **SUMIF function** of Excel with **Asterisk Symbol (*)**.

Enter this formula in any cell of your worksheet:

`=SUMIF(B4:B13,"*Red*",D4:D13)`

Here, weâ€™ve got the sum of all the products with the text **â€śRedâ€ť** in it. It is 691.

**â§Ş**** Explanation of the Formula**

- The
**SUMIF function**takes three arguments: a**range**, a**criteria**, and a**sum_range**. - Here the range is
**B4:B13 (Product Name)**and the**criteria**is**â€śRedâ€ť**. It holds**TRUE**for any text value with the text**â€śRedâ€ť**in it. - Therefore, the formula searches for all the text values in the range
**B4:B13**that contains the text**â€śRedâ€ť**. - When it finds a value in the range
**B4:B13**, it sums the corresponding value from the**sum_range**,**D4:D13**(**Quantity**). - Thus
**SUMIF(B4:B13,â€ť*Red*â€ť,D4:D13)**returns the sum of all the quantities from the range**D4:D13**where the product name contains the text**â€śRedâ€ť**in it.

**â§Ş**** Note to Remember**

- This is a
**Case-Insensitive**formula. That is, if you use**â€śREDâ€ť**or**â€średâ€ť**in place of**â€śRedâ€ť**, it will also work the same.

**5. Use SUMIFS Function to Sum If a Cell Contains a Specific Text in Excel (Case-Insensitive Match)**

You can use the **SUMIFS function** instead of the **SUM function** to sum if a cell contains a specific text in it.

**â§Ş**** How to Accomplish?**

The **SUMIFS** formula to find out the sum of all the products with the text **â€śRedâ€ť** in them will be:

`<span style="font-size: 14pt;">=SUMIFS(D4:D13,B4:B13,"*Red*")</span>`

Here, weâ€™ve again got the sum of all the products with the text **â€śRedâ€ť** in it. It is 691.

**â§Ş**** Explanation of the Formula**

- The
**SUMIFS function**takes a**sum_range**and one or more pairs of**range**and**criteria.** - Here our
**sum_range**is**D4:D13**(**Quantity**). And we have used one pair of a**range**and**criteria**. - The range is
**B4:B13 (Product Name)**, and the criteria is**â€ś*Red*â€ť**. It searches for all the text values in the range**C4:C13**with the text**â€śRedâ€ť**in it. - When it finds a value in the range
**B4:B13**, it sums the corresponding value from the**sum_range****D4:D13**. - Thus
**SUMIFS(D4:D13,C4:C13,â€ť*â€ť)**returns the sum of all the quantities from the range**D4:D13**where the product name has the text**â€śRedâ€ť**in it.

**â§Ş**** Note to Remember**

- This is also a
**Case-Insensitive**formula. That is,**â€śREDâ€ť**or**â€średâ€ť**in place of**â€śRedâ€ť**will also work the same.

**6. Combine SUM, IF, ISERROR, and FIND Functions to Sum If a Cell Contains a Specific Text in Excel (Case-Sensitive Match)**

The previous two methods perform a **case-insensitive** match to sum all the cells that contain a specific text in it.

Now, if you want a **case-sensitive** match, you can apply this formula.

You can combine the **SUM function**, **IF function**, **ISERROR function**, and the **FIND function** to sum if a cell contains a specific text in it with a **case-sensitive** match.

**â§Ş**** How to Accomplish?**

The **Case-Sensitive** formula to find out the sum of all the products with the text **â€śRedâ€ť** in them will be:

`=SUM(IF(ISERROR(FIND("Red",B4:B13)),0,D4:D13))`

**[**Itâ€™s an **Array Formula**. So press **CTRL+SHIFT+ENTER** unless you are using **Office 365**.]

See, weâ€™ve again got the total quantity of products with the text **â€śRedâ€ť** in the name.

**â§Ş**** Explanation of the Formula**

**FIND(â€śRedâ€ť,B4:B13)**searches for a**case-sensitive match**on all the values of the range**B4:B13**(**Product Name**) for the text**â€śRedâ€ť**.- It returns a number if it finds a match, otherwise returns
**#VALUE**error. - So the formula becomes
**SUM(IF(ISERROR({15,#VALUE!,15,#VALUE!,â€¦,#VALUE!}),0,D4:D13)).** **ISERROR({15,#VALUE!,15,#VALUE!,â€¦,#VALUE!})**returns a**TRUE**for each error, and a**FALSE**otherwise.- Therefore, the formula becomes
**SUM(IF{TRUE,FALSE,TRUE,â€¦,FALSE},0,D4:D13)).** **IF{TRUE,FALSE,TRUE,â€¦,FALSE},0,D4:D13)**returns**0**for each**TRUE**, and returns the corresponding value from the range**D4:D13**for each**FALSE**.- Now, the formula becomes
**SUM(D4,0,D5,0,â€¦,0)**. - Finally, the
**SUM function**returns the sum of the corresponding values.

**Things to Remember**

- The
**SUMIF function**and the**SUMIFS function**can be used with**Wildcards (*, ?, ~)**to search for partial matches. - The
**SUMIF function**and the**SUMIFS function**search for a**Case-Insensitive**match, whereas the**FIND function**searches for a**Case-Sensitive**match.

**Conclusion**

Using these methods, you can sum if a cell contains text in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.