If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.
See the image below.
The previous technique is shown in column C – as you can see it can’t handle text entries that aren’t numbers.
Because cell C3 has the word two in it the SUM function in cell C9 returns an error.
Column D has the solution formula using the IFERROR function.
Multiplying a text number by 1 converts it into a real number. But the text in cell C3 will return a #VALUE! error.
The IFERROR multiplies by 1, if that returns a number then that is used. If an error is returned then zero is returned and summed.
The N function
I thought the following formula would work – see column E.
But it appears the N function isn’t compatible with dynamic arrays. It only returned the first entry in the range.