# How to Sum Cells with Text and Numbers in Excel

To sum cells that contain both text and numbers in Excel, you can use a combination of the SUM and VALUE functions along with an array formula. Here is how you can do it:

- First, you need to identify the range of cells that contains the text and numbers. For this example, let's assume the range is A1:A5.
- Click on an empty cell where you want the result of the sum to be displayed. For this example, let's use cell B1.
- Now, enter the following array formula in cell B1:

`=SUM(VALUE(IF(ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A1:A5)),LEFT(A1:A5,LEN(A1:A5)-1),0)))`

- After entering the formula, press Ctrl + Shift + Enter, instead of just Enter, to create an array formula. You will see curly braces
`{}`

appear around the formula, indicating it's an array formula. - The sum of cells with text and numbers will now be displayed in cell B1.

## Example

Let's say we have the following data in cells A1:A5:

```
A1: $100
A2: $200
A3: $300
A4: $400
A5: $500
```

To sum these cells, follow the steps mentioned above and use the array formula in cell B1:

`=SUM(VALUE(IF(ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A1:A5)),LEFT(A1:A5,LEN(A1:A5)-1),0)))`

Press Ctrl + Shift + Enter to create an array formula.

The result in cell B1 will be `1500`

, which is the sum of the cells that contain text and numbers in the range A1:A5.

Did you find this useful?