auto sum
 

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?

auto sum
 

Try this

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

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


"pierre" wrote:

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?

auto sum
 

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

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


"pierre" wrote:

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?

auto sum
 

Hello Pierre,
a1 has a text there, that is the reason excel return 9 on the =sum(a1:a4)

looking for you example a solution could be:

=left(a1,1)+sum(a2:a4)

hth
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"pierre" escreveu:

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?

auto sum
 

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 ?

auto sum
 

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"pierre" wrote:

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?

auto sum
 

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
---------------
Jacob Skaria


"pierre" wrote:


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 ?