# 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: 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.