Calculating Currency with text

[ExcelRoy]

Hi Guys,

Is it possible to calculate simple figures with text

ie

2.450m
2.125m
1.475m

to form a total of 6.050

Thanks

 

[ExcelRoy]

Hi Aladin,

Say i have G11:G20 of values the formula works great

If i am finished with this set of values and delete the contents from G11:G20 the formula shows up and error of #VALUE

I would like to remove this error and say nothing (ie "") until i start entering values into G11:G20

I hope this makes sense

Thanks for your time

 

[Aladin Akyurek]

Hi Aladin,

Say i have G11:G20 of values the formula works great

If i am finished with this set of values and delete the contents from G11:G20 the formula shows up and error of #VALUE

I would like to remove this error and say nothing (ie "") until i start entering values into G11:G20

I hope this makes sense

Thanks for your time

Control+shift+enter, not just enter:

=SUM(IF(G11:G20="",0,SUBSTITUTE(G11:G20,RIGHT(G11:G20),"")+0))

If you want to, custom format the formula cell as:

[=0]"";General

 

[ExcelRoy]

Perfect, Thanks Aladin

 

[Aladin Akyurek]

Perfect, Thanks Aladin

You are welcome.

 

[T. Valko]

Hi Guys,

Is it possible to calculate simple figures with text

ie

2.450m
2.125m
1.475m

to form a total of 6.050

Thanks

Is the text character ALWAYS a lower case "m"?

If you're using Excel 2007 or later...

Array entered**:

=SUM(IFERROR(--SUBSTITUTE(G11:G20,"m",""),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.