+ Reply to Thread
Results 1 to 3 of 3

sum the multiple value in a cell

  1. #1
    Registered User
    Join Date
    01-08-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    1

    sum the multiple value in a cell

    i enter the multiple value in a cell by using alt+enter now i want to sum those values with other cell values

    for ex
    a1 a2 a3
    50 50 25
    100 30
    15

    in a4 cell i want summation of those value (ex:270)

    pls help me

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum the multiple value in a cell

    You would be best served using a User Defined Function for this (VBA), however...

    Assuming you have only three cells to concern yourself with but that number of items within any of those cells can vary you might find using Defined Names and old XLM calls will suffice ?

    Step 1.
    With A1 the active cell create a Named Range as follows:

    Name: =_Eval
    RefersTo: =Evaluate(SUBSTITUTE(A1&CHAR(10)&A2&CHAR(10)&A3,CHAR(10),"+"))

    Now back on your worksheet - in A4 enter:

    =_Eval

    You should find you get 270 returned.

    If you modify the string of numbers in A1:A3 you should find you get different result in A4
    Last edited by DonkeyOte; 01-08-2012 at 07:27 AM. Reason: missing all important: "however"

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum the multiple value in a cell

    edit to prior post:

    to account for possibility of non-numerics and or blanks...

    =Evaluate("SUM("&SUBSTITUTE(A1&CHAR(10)&A2&CHAR(10)&A3,CHAR(10),",")&")")

    though the above is more robust it would have the same constraints as a traditional SUM function in terms of number of numbers permitted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1