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.
=SUM(IFERROR(D2:D7*1,0))
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.
=SUM(N(E2:E7))
But it appears the N function isn’t compatible with dynamic arrays. It only returned the first entry in the range.
Related Posts
I presume you are using either XL365 or XL2021 because I had to array-enter your solution (Column D) formula in order to get it to work in my copy of XL2019. With that said, here is an alternative formula that can be normally entered into your copy of Excel…
=SUM(0+TEXT(D2:D7,”0;0;0;\0″))
For XL2019 (and earlier) versions of Excel, this normally entered formula also works…
=SUMPRODUCT(0+TEXT(D2:D7,”0;0;0;\0″))
Hi Rick
Yes this is a subscription version solution (I mentioned that in the previous post – will add it to this post as well).
Thanks for the extra solutions – another interesting use of the TEXT function.
Regards
Neale
I should have mentioned that my formulas are set for whole number values in the cells (to match your example). If there could be floating point numbers in the cells, then each of the first two zeros should be replaced with the word General