Excel: How to Sum Cells with Text and Numbers


You can use the following basic formula to sum cells in Excel that contain both numbers and text:

=SUM(SUBSTITUTE(B2:B8, "some_text", "")+0)

This particular formula removes the text string “some_text” from each cell in the range B2:B8 and then calculates the sum of the values in the range B2:B8.

The following examples show how to use this formula in practice.

Example 1: Calculate Sum of Cells with Text and Numbers

Suppose we have the following dataset that shows the total number of sales at seven different stores:

To calculate the sum of sales, we can type the following formula into cell B10:

=SUM(SUBSTITUTE(B2:B8, " items", "")+0)

Once we press Enter, the sum of the items will be shown:

Excel sum cells with text and numbers

The sum of the items sold is 97.

This formula simply substituted a blank where “items” used to be in each cell and then calculated the sum of the values remaining in the cells.

Example 2: Calculate Sum of Cells with Different Text and Numbers

Suppose we have the following dataset that shows the total number of sales at seven different stores:

To calculate the sum of sales, we can type the following formula into cell B10:

=SUM(SUBSTITUTE(SUBSTITUTE(B2:B8, " items", ""), "things", "")+0)

Once we press Enter, the sum of the values in column B will be shown:

The sum of the items sold is 97.

This formula simply substituted a blank where “items” and “things” used to be in each cell and then calculated the sum of the values remaining in the cells.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Replace Blank Cells with Zero in Excel
How to Replace #N/A Values in Excel
How to Sum If Cells Contain Text in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *