issue with "left"
 

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 ?

issue with "left"
 

I just tested your formula using commas (US) and it worked fine.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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 ?

issue with "left"
 

Try the below

=SUMPRODUCT(IF(A1:A11<"",VALUE(SUBSTITUTE(A1:A11, "*",""))))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"pierre" wrote:

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 ?

issue with "left"
 

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 ?

issue with "left"
 

Oops; please ignore the previous post...I misunderstood..Try the below instead

=SUMPRODUCT(VALUE(LEFT(A1:A2,FIND("*",A1:A2&"*")-1)))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below

=SUMPRODUCT(IF(A1:A11<"",VALUE(SUBSTITUTE(A1:A11, "*",""))))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"pierre" wrote:

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 ?

issue with "left"
 

Revised to handle blank entries

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=SUMPRODUCT(IF(A1:A11<"",VALUE(LEFT(A1:A11,FIND(" *",A1:A11&"*")-1))))

If this post helps click Yes
---------------
Jacob Skaria


"pierre" wrote:

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 ?