SUM function and units issue

[SuperJam]

Hi Folks,

I am trying to SUM figures with miles (mi) inside the same cell. I tried to use this already to disregard the miles unit but it gives me a #VALUE error.

=SUM(SUBSTITUTE(A2:A7, "mi", "") +0)

Any other suggestions?

Thanks.

 

[sericom]

It works for me, did you Ctrl+Shift+Enter for an array formula?
Or maybe there's some other character in your data somewhere.

 

[Dave Patton]

Excel 2010
	A	B	C
1			454
2	12 mi
3	15mi		454
4	200 mi
5	12 mi
6	15mi
7	200 mi
5b

Cell Formulas
Range	Formula
C3	=SUMPRODUCT(SUBSTITUTE(A2:A7,"mi","")+0)
C1	{=SUM(SUBSTITUTE(A2:A7,"mi","")+0)}
Press CTRL+SHIFT+ENTER to enter array formulas.