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:

1. 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.
2. Click on an empty cell where you want the result of the sum to be displayed. For this example, let's use cell B1.
3. 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)))

4. 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.
5. 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.