Sum only numbers?!
 

Assume values are in A1:E1 and non numeric entries start with a single
letter.
=SUM(IF(ISNUMBER(A1:E1),A1:E1,RIGHT(A1:E1,LEN(A1:E 1)-1)*1))
Entered with Ctrl+Shift+enter rather than just enter because this is an
array formula.

--
Regards,
Tom Ogilvy

"dove_g" wrote in message
...
Row in excel have following values:

9 12 P4 3 P1


In sum I must get this results:

SUM (9 + 12 + 4 + 3 + 1 ) = 27
SUM P (1 + 1) = 2

Is that possibe, I have try few formulas without success. Problem is
only how to calculate (SUM) all this.

Thank you.


P.S.
I have only letter P, on the left side, so I can use LEFT(x,1)="P".

Sum only numbers?!
 

On Wed, 3 Sep 2003 07:34:15 -0400, "Tom Ogilvy"
wrote:

Assume values are in A1:E1 and non numeric entries start with a single
letter.
=SUM(IF(ISNUMBER(A1:E1),A1:E1,RIGHT(A1:E1,LEN(A1: E1)-1)*1))

Hm, that's not vorking at all?! No value.

Sum only numbers?!
 

It works fine - given the assumptions. It was copied from the formula bar -
the cell was displaying the correct sum. If it shows just the sum of the
numbers not preceded by letters, then you have not entered it with with
Ctrl+Shift+Enter as instructed.

However, you say it is displaying nothing - hard for a working function to
do that - is the cell displaying the formula. Do you have a space in front
of the Equal sign?

--
Regards,
Tom Ogilvy



"dove_g" wrote in message
...
On Wed, 3 Sep 2003 07:34:15 -0400, "Tom Ogilvy"
wrote:

Assume values are in A1:E1 and non numeric entries start with a single
letter.
=SUM(IF(ISNUMBER(A1:E1),A1:E1,RIGHT(A1:E1,LEN(A1: E1)-1)*1))

Hm, that's not vorking at all?! No value.

Sum only numbers?!
 

On Wed, 3 Sep 2003 07:34:15 -0400, "Tom Ogilvy"
wrote:

Assume values are in A1:E1 and non numeric entries start with a single
letter.
=SUM(IF(ISNUMBER(A1:E1),A1:E1,RIGHT(A1:E1,LEN(A1: E1)-1)*1))

Sorry Tom, it's Ok now.
I see now that I must have *1 in RIGHT command so with that I get
value number.

And point is if there is any field empty, empty * 1 gives no value
result.

So I just add small code:
SUM(IF(LEN(A1:E1)=0;0;your formula)

Thank you a lot Tom.