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.

**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.

**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**.

**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.

## Related Articles

**How to Use SUMIF with Multiple Criteria in Excel (3 Methods)**

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

**How to Do SUMIF by Month and Year in Excel (7 Ways)**