How to Sum If Cell Contains Number and Text in Excel

Get FREE Advanced Excel Exercises with Solutions!

It is an informative article that provides solutions to a common problem encountered by Microsoft Excel users. This article discusses the challenge of summing cells that contain a mix of numbers and text, which can be complicated due to the different data types involved. The article offers practical solutions to overcome this issue by utilizing Excel’s SUMIF, SUMIFS, ISNUMBER, ISTEXT, SUMPRODUCT, IF, FIND, and VALUE functions and creating a custom formula using a combination of other functions to sum if cell contains number and text. The article provides easy-to-follow instructions that help users achieve accurate calculations and streamline their workflow when working with mixed data types in Excel.


Sum If Cell Contains Numbers and Text: 3 Suitable Ways

The dataset contains sales records of the Continental Group for January. It includes information such as the date of the sale, the name and contact information of the customer, the product sold, and the amount of the sale. The products sold in this dataset are laptops, PS4s, PCs, HDDs, and SDDs.

Dataset of Continental Group

Today’s task is to add the text and number-filled cells from this data source.


1. Sum If Cell Contains Text

In this section, we will try adding the quantities of products whose customers’ addresses are Email IDs rather than Phone Numbers.

That is, if the neighboring cell has text as the Customer Address, we must add the Quantity of that field.


1.1  Using SUMIF Function to Sum if a Cell Contains Text in Excel

Excel’s SUMIF function can be used to sum if a cell contains the text.

Using the SUMIF function to sum if cell contains number and text

To do so, use an Asterisk Symbol (*) as the condition in a SUMIF function, as seen in the formula below:

=SUMIF(D5:D11,"*",F5:F11)

We have got the total amount is 1720. Which only has text values in the adjacent cells in the Customer Address column.


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

We can also use SUM, IF, and ISTEXT functions to sum if a cell contains text in Excel.

Select any cell on the worksheet and then Enter the following formula:

=SUM(IF(ISTEXT(D5:D11),F5:F11,0))

We’ve got the same total amount of products with customers having text addresses, 1720.

Note: It’s an array formula, So if you aren’t a Microsoft 365 user, use CTRL+SHIFT+ENTER.

Formula Breakdown:

ISTEXT(D5:D11) checks each value in the range D5:D11 and returns a TRUE if it’s a text value. Otherwise, it returns a FALSE.

It returns the corresponding value from the range F4:F11 for each TRUE. And for each FALSE, it returns 0.

Therefore the formula becomes SUM(0,F6,F7,0,0,F10,0).

Now the SUM function returns the sum of the corresponding values from the range F4:F11.


1.3 Utilizing SUMIF Function to Sum By Using Cell Number

We can also use a cell number that contains text to sum. By using a cell number we can find specific text in the dataset and sum the value according to our criteria.

Using cell number sum if cell contains specific text in that text

Here we select cell number H8 and enter the following formula:

=SUMIF(E5:E11,"*"& H7 &"*",F5:F11)

The total amount of Laptops is 2020.

Short Explanation of the Formula: 

The SUMIF function will look for the text value of cell H7.

So, the SUMIF function searches for “Laptop” in the cells E5:E11. It returns True if “Laptop” is found. Otherwise “False

Now the SUMIF function returns the sum of the corresponding values from the range F4:F11.


1.4 Use Excel’s SUMIF Function (case-insensitive match) to sum if a cell contains a specific text.

So far, we’ve added up all the cells with text values.

Now we’ll attempt something a little different. We’ll add up the cells that have text values with a certain text.

For example, try adding up the total amount of pc.

That means we must sum any cell that contains the text “pc” in it.

We may also do this with Excel’s SUMIF function and the Asterisk Symbol (*).

Using the asterisk symbol for a partial match to sum if the cell contains text(case-insensitive match)

Enter the following formula in any cell of your datasheet:

=SUMIF(E5:E11,"*pc",F5:F11)

The total amount of “pc” product is 2200.

Note: This is a Case-Insensitive formula. We can use it for all types of cases, uppercase, and lowercase. That is if you use “PC” or “pc” in place of “Pc”, it will also work the same.


1.5 Integrate SUMPRODUCT, ISNUMBER, and FIND Function  (case-sensitive match) to sum if a cell contains a specific text.

In the previous approach, we use a case-insensitive match to total all cells that contain a specific text.

Now we will apply a formula combination of SUMPRODUCT, ISNUMBER, and FIND functions for a case-sensitive match.

Utilizing a combination of SUMPRODUCT, ISNUMBER, and FIND functions for a case-sensitive exact match to sum if the cell contains text

The Case-Sensitive formula for calculating the sum of all products containing the text “Pc” will be:

=SUMPRODUCT(ISNUMBER(FIND("Pc",E5:E11))*F5:F11)

The outcome is 1430. There is only one cell containing the text “Pc

Note: It’s an Array Formula. So press CTRL+SHIFT+ENTER unless you are using Microsoft Office 365.

Read More: How to Use Excel SUMIF with Greater Than Criterion


1.6 Applying SUMIFS Function to Sum If a Cell Contains Text in Excel

To sum if a cell contains text, we can use the SUMIFS function rather than the SUMIF function in Excel.

Applying SUMIFS functions to sum if a cell contains  text

To total amounts with the text addresses, the SUMIFS formula will be:

=SUMIFS(F5:F11,D5:D11,"*").

The total amount of products customers have text addresses is 1720.

Formula Breakdown:

The SUMIFS function takes a sum_range and one or more pairs of range and criteria.

Here our sum_range is F5:F11 (Amounts). And we have used one pair of a range and criteria.

The range is D5:D11 (Contact Address), and the criteria is “*”. It searches for all the text values within the range D5:D11.

When it finds a text value in the range D5:D11, it sums the corresponding value from the sum_range F5:F11

Thus SUMIFS(F5:F11,D5:D11,”*”) returns the sum of all the amounts from the range F5:F11where the corresponding address in the range D5:D11 is a text address.


2. Sum Cells Containing Numbers Only

In this article, so far we only work with those cells that contain only text. Now we are going to show you how to sum if the cell contains numbers only.

We are using a combination of some Excel functions in this part.


2.1 Using Combination of SUM, IF, and ISNUMBER Functions to Sum if a Cell Contains Number in Excel

We can use SUM, IF, and ISNUMBER functions to sum up if a cell contains a number in Excel.

Using a combination of SUM, IF, and ISNUMBER to sum if a cell contains a number only

Select a cell on the worksheet and Enter the formula below:

=SUM(IF(ISNUMBER(D5:D11),F5:F11,0))

So, the Total amounts of products, the customer has an address containing numbers(Telephone Number) is 3190.

Formula Breakdown:

  • ISNUMBER(D5:D11) returns an array of TRUE or FALSE values, indicating whether each cell in the range D5:D11 contains a number or not.
  • IF(ISNUMBER(D5:D11),F5:F11,0) returns an array of values, where each value is either the value in the corresponding cell in the F range (if the corresponding cell in the D range contains a number), or 0 (if the corresponding cell in the D range does not contain a number).
  • Finally, SUM(IF(ISNUMBER(D5:D11),F5:F11,0)) calculates the sum of the array returned in step 2, which gives us the total sum of the values in the F range for cells where the corresponding cell in the D range contains a number.

3. Sum Cell Containing Both Texts and Numbers

We are going, to sum up, both text and number in this section. We change the dataset slightly for this section so that you can understand this part easily. The we are going to calculate the total storage capacity. But the problem is there are two types of storage in the dataset. So, we have to apply a formula by which we can calculate both types of storage separately.

So you have to add different storage types in a separate cell.

For example, we added GB(Gigabyte) and TB(Terabyte) in cells H7 and H8 accordingly.


3.1 Combining Several Excel Functions to Sum if a Cell Contains Both Text and Numbers in Excel

Using Excel functions to sum if a cell contains a number and text

Now, Select any cell on your dataset. Here we took I7 and Enter the formula below:

=SUM(IF(ISNUMBER(FIND(H7,$F$5:$F$11)),VALUE(LEFT($F$5:$F$11,FIND(H7,$F$5:$F$11)-1)),0))

Thus, the total Storage capacity is 1684GB and 8TB

Note:  It’s an Array Formula. So press CTRL+SHIFT+ENTER unless you are using Microsoft Office 365

Formula Breakdown:

FIND(H7,$F$5:$F$11) returns an array of the position of the substring H7 in each cell in the range $F$5:$F$11. If the substring is not found, it returns an error.

ISNUMBER(FIND(H7,$F$5:$F$11)) converts the array from step 1 into an array of TRUE or FALSE values, where TRUE indicates that the substring is found in the corresponding cell and FALSE indicates that it is not found.

LEFT($F$5:$F$11,FIND(H7,$F$5:$F$11)-1) returns an array of the leftmost characters in each cell in the range $F$5:$F$11 up to the position of the substring H7. If the substring is not found, it returns an error.

VALUE(LEFT($F$5:$F$11,FIND(H7,$F$5:$F$11)-1)) converts the array from step 3 into an array of numerical values.

IF(ISNUMBER(FIND(H7,$F$5:$F$11)),VALUE(LEFT($F$5:$F$11,FIND(H7,$F$5:$F$11)-1)),0) returns an array of numerical values or 0, depending on whether the substring is found in each cell in the range $F$5:$F$11 or not.

SUM(IF(ISNUMBER(FIND(H7,$F$5:$F$11)),VALUE(LEFT($F$5:$F$11,FIND(H7,$F$5:$F$11)-1)),0)) calculates the sum of the array returned in step 5, which gives us the total sum of the numerical values extracted from cells that contain the substring H7.


4. Sum Numbers in a Cell with a User-Defined Function That Ignores Text

Now we are trying to solve a different problem. We want to sum only the numerical value from the string which contains both text and numerical values. We will create a user-defined function to do that.

The pasting procedure of the VBA code

  • Now, Press. ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
  • Click Insert >> Module, and paste the following code in the Module Window.

Code:

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Update by Exceldemy
    Dim NUM As Variant, Total As Long
    NUM = Split(rngS, strDelim)
    For Total = LBound(NUM) To UBound(NUM) Step 1
        SumNumbers = SumNumbers + Val(NUM(Total))
    Next Total
End Function

After pasting, save and close the code and go back to the worksheet.

Utilizing a user-defined function, to sum up only numbers from a cell containing both texts and numbers

Now, Enter the below formula on your worksheet,

=Sumnumbers(E5)

then drag the fill handle down to the cells you want to fill the formula, and only numbers in each cell are added together, see the picture above.

Here E5 indicates the cell where you only want to sum up the numbers.


Frequently Asked Questions

1. How do I sum cells with text and numbers in Excel?

To sum cells with text and numbers in Excel, you can use the SUMIF function. Here’s how you do it:

Choose a cell where you want to see the sum.

Type “=SUMIF(range, criteria, sum_range)” in the cell.

  • In the “range” section, select the cells you want to sum up.
  • At the “criteria” section, type the text and number you want to sum up.
  • In the “sum_range” section, select the cells you want to sum up that match the criteria.

Press “Enter” to see the sum of the cells that contain the text and number you entered.

If the cells have both text and numbers, you may need to use other functions like LEFT, RIGHT, and SUM to separate and add the numbers and text separately.

2. How do I add to the sum if a cell contains text in Excel?

To add to the sum of cells that contain text in Excel, you can use the SUMIF function with wildcards.

For example, if you want to add up the values in cells that contain the word “orange“, you would enter “=SUMIF(A1:A10, “orange”, B1:B10)” in the cell where you want to see the sum. This formula would add up the values in cells B1 to B10 where the corresponding cells in A1 to A10 contain the word “orange“, no matter what other characters are before or after it.

Remember that the SUMIF function is not case-sensitive, so it will match text regardless of whether it is in uppercase or lowercase.

3. How do I combine text and numbers in two cells in Excel?

To combine text and numbers in two cells in Excel, you can use the CONCATENATE function.

For example, if you want to combine the text “Order #” in cell A1 with the number “123” in cell B1, you would enter “=CONCATENATE(A1, B1)” in the cell where you want to see the combined result. This formula would give you “Order #123“.


Things to Remember

  • In this article, we changed our dataset a few times. While practicing please take a close look at the dataset.
  • There are several array formulas in this article. Make sure that you’re a Microsoft 365 user or simply press CTRL+SHIFT+ENTER

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In conclusion, the article provides useful information and tips on how to use the Excel functions in Excel to calculate the sum of cells that contain both numbers and text, along with examples and screenshots to illustrate how it can be applied in different scenarios. By following the steps outlined in the article, users can easily and efficiently calculate the sum of cells that meet specific criteria in their Excel spreadsheets. Overall, the article is a helpful resource for anyone who needs to perform calculations on mixed data types in Excel. If you have any queries or suggestions, please do comment in the comments section below.


Related Articles


<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mizbahul Abedin
Mizbahul Abedin

Md Mizbahul Abedin, BSc, Textile Engineering and Management, Bangladesh University of Textiles, has been working with the ExcelDemy project for 11 months. Currently working as an Excel and VBA Content Developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He has published almost 20 articles in ExcelDemy. He has passions for learning new things about Microsoft Office Suite and Data analysis. Besides, he also likes to travel, photography, international politics, and read... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo