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:

Please Login or Register  to view this content.

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

You could try:

=SUM(SUBSTITUTE(A1:A7,"#","")+0)

i forgot...

You seem to be using an older version of Excel than me. So, please refer to the attached file (above). If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You're welcome!!