• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to sum the content of a cell

D

dophile

New Member
I have a cell (A1)value reads as 4/4/3/4/4, I want the result in cell (A2) of the sum as =4+4+3+4+4= 19.
how can i write a formula. Please help. Thanks
 
You can utilize legacy Evaluate Macro function from Excel 4.0.

Set up:
1. Select Cell A2
2. Launch Name Manager and create new named range "SumCell"
3. Formula for the named range will be...
=EVALUATE(SUBSTITUTE(Sheet1!A$1,"/","+"))

4. In Cell A2 type =SumCell

Note: It is very important that you select Cell A2 when you create Named Range as column reference is left relative and will change as you copy formula across.

I assumed you had other values across row1 and needed formula to be somewhat dynamic.

Alternately you can do something like...
=SUM(IFERROR(VALUE(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-1,0)),1)),0))
Edit: Forgot to mention above formula must be confirmed as array (CTRL+SHIFT+ENTER)

However, this will only work with single digit integer values (i.e. it will not solve for 11/12/13 = 36, it will solve to 9).
 
Last edited:
Chihiro said:
You can utilize legacy Evaluate Macro function from Excel 4.0.

Set up:
1. Select Cell A2
2. Launch Name Manager and create new named range "SumCell"
3. Formula for the named range will be...
=EVALUATE(SUBSTITUTE(Sheet1!A$1,"/","+"))

4. In Cell A2 type =SumCell

Note: It is very important that you select Cell A2 when you create Named Range as column reference is left relative and will change as you copy formula across.

I assumed you had other values across row1 and needed formula to be somewhat dynamic.

Alternately you can do something like...
=SUM(IFERROR(VALUE(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-1,0)),1)),0))
Edit: Forgot to mention above formula must be confirmed as array (CTRL+SHIFT+ENTER)

However, this will only work with single digit integer values (i.e. it will not solve for 11/12/13 = 36, it will solve to 9).
Click to expand...


Chihiro, Thank you for your prompt reply much appreciated.
 
Hi ,

In a general case , Haseeb's formula would be :

=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ", DigitsPerElement * NumberOfElements)),ROW(INDEX(A:A, 1):INDEX(A:A, NumberOfElements))* DigitsPerElement * NumberOfElements-(DigitsPerElement * NumberOfElements - 1),DigitsPerElement * NumberOfElements))))

For the case of 50 elements (NumberOfElements = 50) , and 2 digit numbers (DigitsPerElement = 2) , this would become :

=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ", 100)),ROW(INDEX(A:A, 1):INDEX(A:A, 50))* 100 - 99,100))))

Narayan
 
You must log in or register to reply here.
Back
Top