Sum If a Cell Contains Text in Excel (6 Suitable Formulas)

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.

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:

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:

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:

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

Read More: If Cell Contains Text Then Copy to Another Sheet in Excel

Similar Readings

• Sum Cells in Excel: Continuous, Random, With Criteria, etc.
• How to Assign Value If Cell Contains Word in Excel (4 Easy Ways)
• If Cell Contains Text Then Return Value in Another Cell Using Excel Formula
• How to Return Value If Cells Contain Certain Text from a List
• Find Text in Excel Range and Return Cell Reference (3 Ways)

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:

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.

Read More: If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)

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:

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.

Read More: If Cell Contains Text Then Add Text in Another Cell in Excel

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 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:

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

Read More: Excel If Cell Contains Text Then Return Value (8 Easy Ways)

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)
• Do SUMIF by Month and Year in Excel (7 Ways)
• How to SUM with IFERROR in Excel (4 Ways)
• Check If Cell Contains Partial Text in Excel (5 Ways)
• How to Use VLOOKUP If Cell Contains a Word within Text in Excel