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.