How to Sum Cells Containing Text in Excel

Manually totaling spreadsheet cells that mix numeric values with descriptive text is a frustrating bottleneck for busy professionals. Standard funding sources-like municipal grants or corporate sponsorships-are frequently logged with qualitative status notes directly in the amount column, breaking standard SUM functions. Fortunately, mastering advanced array formulas grants immediate, automated clarity over your budgeting data. As a key stipulation, your dataset must maintain consistent formatting rules for the text extraction to work flawlessly. For example, isolating numbers from cells containing "$5,000 Approved" or "$1,200 Pending" provides reliable, real-time financial tracking. Below, we examine the precise formula configurations to successfully sum these mixed-text cells.

Excel Formula to Sum Cells with Text: A Complete Guide

Excel is an incredibly powerful tool for data analysis, but it can be highly sensitive to data formatting. One of the most common issues users face is attempting to sum a range of cells, only to find that Excel returns 0 or ignores certain values. This almost always happens because some of the numbers are being treated as text.

Whether you have numbers stored as text, text mixed with numbers (like "10 USD" or "15kg"), or you need to sum numbers based on a text condition in an adjacent column, this guide will walk you through the exact formulas and techniques to solve your problem.

Scenario 1: Summing Numbers Stored as Text

Sometimes, numbers are imported from external databases or web software as text. You can often identify these by a small green triangle in the top-left corner of the cell, or because they align to the left side of the cell by default.

If you use the standard =SUM(A1:A5) formula on these cells, Excel will ignore them and return 0.

Solution A: The SUMPRODUCT Formula (No Array Entry Needed)

The easiest way to sum numbers stored as text without modifying the original data is by using the SUMPRODUCT function combined with the double unary operator (--). The double negative coerces the text numbers into actual numerical values that Excel can calculate.

=SUMPRODUCT(--(A1:A5))

How it works:

• A1:A5 represents the range containing your text-formatted numbers.
• The double negative (--) forces Excel to convert the text strings (e.g., "10") into mathematical numbers (10).
• SUMPRODUCT then adds those converted numbers together.

Solution B: The SUM and VALUE Array Formula

If you prefer using traditional functions, you can nest the VALUE function inside a SUM formula. This converts text to numbers before summing.

=SUM(VALUE(A1:A5))

Note: If you are using Excel 2019 or earlier, you must press Ctrl + Shift + Enter after typing this formula to enter it as an array formula. Excel 365 and Excel 2021 users can simply press Enter.

Scenario 2: Summing Cells with Mixed Text and Numbers

Often, cells contain both numbers and text characters, such as unit measurements (e.g., "10kg", "50kg") or currency symbols added manually (e.g., "100 USD"). Because of the text suffix or prefix, Excel treats the entire cell as text.

Solution A: Using SUBSTITUTE to Strip Text (For Uniform Suffixes)

If all your cells share the same text suffix (for example, " USD"), you can use the SUBSTITUTE function to temporarily remove the text, convert the remaining string to a number, and sum them up.

=SUMPRODUCT(SUBSTITUTE(A1:A5, " USD", "")+0)

How it works:

• SUBSTITUTE(A1:A5, " USD", "") searches for " USD" in each cell and replaces it with an empty string (nothing).
• Adding +0 at the end of the substitution forces Excel to convert the resulting text number into an actual number.
• SUMPRODUCT sums the numerical array.

Solution B: Using New Excel 365 Functions (TEXTBEFORE)

If you are using modern Excel (Excel 365 or Excel 2024), you can leverage the powerful TEXTBEFORE function to extract numbers that appear before a specific delimiter, like a space.

=SUM(VALUE(TEXTBEFORE(A1:A5, " ")))

This formula extracts everything before the space, converts it to a value, and sums it. This is highly efficient if you have varying text elements like "10 kg", "15 lbs", or "20 pcs".

Scenario 3: Summing Values Based on a Text Criterion (SUMIF)

Sometimes your numbers are perfectly formatted as numbers, but you only want to sum them if an associated cell contains a specific text string. For this, you use SUMIF or SUMIFS.

Exact Match Text Summing

To sum all sales for exactly "Apple":

=SUMIF(A1:A4, "Apple", B1:B4)

Partial Match Text Summing (Using Wildcards)

If you want to sum sales for any item containing the word "Apple" (including "Apple Gold"), use the asterisk (*) wildcard character:

=SUMIF(A1:A4, "*Apple*", B1:B4)

The asterisks tell Excel to match any text that has "Apple" anywhere inside it, regardless of what comes before or after.

Scenario 4: Summing Ranges that Contain Pure Text and Numbers

Sometimes you have a column with a mix of actual numbers and pure text placeholders (like "N/A", "Pending", or "None"). If you use a basic formula, these text fields can trigger errors or be difficult to filter.

If you want to extract numbers from a range that contains some entries that are completely text, you can use an IFERROR wrapper inside your array formulas to treat text entries as zero:

=SUM(IFERROR(VALUE(A1:A5), 0))

(Remember to press Ctrl + Shift + Enter in older Excel versions).

This formula attempts to convert every cell in A1:A5 to a number. If it succeeds, it keeps the number. If it fails (because the cell contains a word like "Pending"), IFERROR converts that failure to a 0, allowing the sum to complete without returning an error.

Pro-Tip: Prevent Text Formatting Issues with Custom Formatting

The best way to handle text and numbers together is to avoid entering text inside your numerical cells altogether. Instead of manually typing "10 kg", you can type "10" and let Excel handle the "kg" display using Custom Number Formatting. This keeps the data numerical, allowing you to use standard, fast SUM formulas.

1. Select the cells you want to format.
2. Right-click and select Format Cells (or press Ctrl + 1).
3. Go to the Number tab and select Custom.
4. In the Type input field, enter: # "kg" or #,##0 "USD".
5. Click OK.

Now, Excel will display the units visually, but behind the scenes, it treats the cells as pure numbers, meaning a basic =SUM(A1:A5) will work flawlessly!

Summary of Formulas