Summing numbers in a column range (X1...Xn) that may contain a non number character

I've got an interesting problem:

Given a column containing one of two possibilities:

1. A Number
2. Text that contains numbers with a single common character

Get the sum of the column such that:

1. The excel document can not use Scrips or Macros, since the document must be opened on an apple handheld device (example: IPAD, IPHONE) and due to apples "safety" policies, macros are disabled on such devices.
2. The column containing said data is not modified in any way and
3. cells that contain numbers with X single common character, where X is a character, include the numerical part of that text in the sum calculation

I've attached a picture of an example:

A.png

But for example:

if a column contains the following set of numbers [1,3,5,7,9,11#,13]:

In column form:
1
3
5
7
9
11#
13

The sum of this set of numbers must return 49.

A simple formula =SUM(x1:x:n), where n is a number, would not work since excel disregards cells that contain a mix of numbers + the "#" character.

Thus the solution I need is to get a formula that would correctly get 49 and not 38.

A formula I'm trying to use is incorrect since I'm using SUBSTITUTE which would modify the contents of the data which I'm not allowed to so.

I may be approaching the problem incorrectly too, but it is:

=IF(SUM(O5:O12)<>0,SUM(O5:O12),"")+IF(NOT(ISNUMBER(O5:O12)),SUM(SUBSTITUTE(O5:O12,"$","")<>0,,""))

The logic I have in mind is:

1. If the sum of the column is <> 0, get the sum of the column THIS will return 38
2. plus, If the cell contains "#", ignore "#" when summing for the total