As your example is posted, the formula will return an error because in all
of the LEFT functions you're looking for "*" but not all of those cells
contain the "*".

If you want to sum all the "regular" numbers plus any that have an "*" as
the rightmost character try this array formula** :

=SUM(A1:A11)+SUM(IF(RIGHT(A1:A11)="*",--LEFT(A1:A11,LEN(A1:A11)-1)))

** 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.

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hello
i have he following formula :

=SUM(a1:a4;a6;a8;a10)+LEFT(a5;FIND("*";a5)-1)+LEFT(a7;FIND("*";a7)-1)+LEFT(a9;FIND("*";a9)-1)+LEFT(a11;FIND("*";a11)-1)

in example A5 contains : =15&""&"*"
in example A7 contains : = 21&""&"*k"
in example A9 contains : = 13&""&"*h"
in example A11 contains : 23&""&"*d"

as you can see my primary goal is to find a formula which makes me only a
SUM for the values wich are from A1 to A11

is there another shorter formula to do that ?