issue with "left"

  • Thread starter Thread starter pierre
  • Start date Start date
P

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 ?
 
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
 
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.
 
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
 
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
 
Back
Top