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:

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