How to SUM Text Numbers in Excel

SUMPRODUCT to the rescue

When data is imported into Excel sometimes the values come in as text rather than values. Most functions can’t perform any calculations with text numbers, but one can. See how easy it is to add up text values.

There are a few ways to convert text numbers to real numbers. If you need to add up text numbers the technique below works well.

In the image below you can see that the SUM function (column B) returns a zero when adding up text values.

You can also see that a basic SUMPRODUCT (column C) will also return zero. A simple change to SUMPRODUCT in column D fixes the issue.

By adding brackets around the range and including *1 in the formula you force (coerce) Excel into converting the text values into real values.

See the image below to see how/why this technique works.

If you highlight part of a formula and press the F9 function key it will calculate just that part of the formula.

Including the *1 forces Excel to multiply all the text numbers by 1, which converts them into real numbers. SUMPRODUCT then adds them up. Yet another reason it is my favorite function.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *