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
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
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"
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks