issue with "left"

[pierre]

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 ?

 

[Don Guillett]

I just tested your formula using commas (US) and it worked fine.

 

[Jacob Skaria]

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

 

[T. Valko]

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.

 

[Jacob Skaria]

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]

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