auto sum

[pierre]

hello guys...
need help please

i have :

in cell A1 , this formula =1&""&"*"
in cell A2 ... 3
in cell A3... 4
in cell A4.... 2

i made an autosum in cell A5 (from A1 to A4) and the result is 9 although i
would like it to be 10

what should i do or modify.....any suggestion?

 

[Jacob Skaria]

Try this

=SUM(A2:A4)+LEFT(A1,FIND("*",A1)-1)

If this post helps click Yes

 

[Gary''s Student]

=SUM(VALUE(SUBSTITUTE(A1:A4,"*","")))

This is an array formula that must be entered with:
CNTRL-SHFT-ENTER
rather than just the ENTER key.

 

[pierre]

starting withis formula :
=SUM(A2:A4)+LEFT(A1,FIND("*",A1)-1)

suppose i would like to add more "left" like :

=SUM(A2:A4)+LEFT(A1,FIND("*",A1)-1)+LEFT(A10,FIND("*",A10)-1)+
LEFT(A11,FIND("*",A11)-1).....

how can i shorcut that ?

 

[Shane Devenshire]

Hi,

Here is my prefered solution -
1. replace your formula in A1 with 1
2. with A1 selected choose Format, Cells, Number tab, Custom and on the Type
line use something like
General"*"
or
0.0"*"

 

[Jacob Skaria]

You can try this; you will have to make sure the all cells have values;
either a 0 or a valid value

=SUMPRODUCT(VALUE(SUBSTITUTE(A1:A20,"*","")))

If this post helps click Yes