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.

Data Set to Sum If a Cell Contains Text in Excel

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)

SUMIF Formula to Sum If a Cell Contains Text in Excel

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

Combined Formula to Sum If a Cell Contains Text in Excel

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

Combined Formula to Sum If a Cell Contains a Specific Text in Excel

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


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)

How to SUM with IFERROR in Excel (4 Ways)

SUM Ignore N/A in Excel( 7 Easiest Ways)

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo