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.
Is it possible to change the SumNumbers formula to add all the numbers in a column of mixed cells? For example, each column has 1A, 2B, 3C and I would like the formula at the bottom of the column to tell me 6.
If they were all 2 digits only you could use something like
=SUMPRODUCT(LEFT(D2:D4)*1)
If the length was variable and with just a single letter on the end use
=SUMPRODUCT(LEFT(D2:D4,LEN(D2:D4)-1)*1)
Hope that helps.
Regards Neale
This was so helpful. Finally was able to fix my spreadsheets. Thank you so much.