LinkBack URL
About LinkBacks
Hello,
Is there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.
Any help with this is appreciated.
Dan
SUM of values in a comma delimited cell
Hello,
Is there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.
Any help with this is appreciated.
Dan
Last edited by Danexcel; 01-15-2010 at 11:19 AM.
Dan,
=SUM(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1))
Last edited by Excelfriend; 01-13-2010 at 04:33 PM.
While that will do the job, and is acurate based on the limited data provided, it also makes some dangerous assumptions.Originally Posted by Excelfriend
Dan, you need to give a better picture of your situation. Two questions immediately pop to mind: Is your data always single digit numbers? Is it always groups of four?
Last edited by T.J. Lanza; 01-13-2010 at 04:35 PM. Reason: Adjusting order of quotes.
Thank you!
If that A1 cell data suddenly becomes just 1,4,6 will the formula you mentioned handle it?
Is there not a general formla that sums.
By the way, what is the SUM formula if I have 1,4,6 ?
Thanks
Dan
The point being made is whether or not the "Numbers" are always single digits.
One approach would be to use a Name... insert a new name as follows:
ThenName: EVAL RefersTo =Evaluate("SUM("&INDIRECT("RC[-1]",FALSE)&")")
EDIT: the above is just another variant on martin's prior postB1: =EVAL where A1 holds delimited string
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
(I changed my mind about what I said now)
Last edited by Danexcel; 01-13-2010 at 04:41 PM.
Dan,
No my formula can't handle 1,4,6.
I'm not able for a better one, but I'm sure there are others who are able to help you.
Thank you excelfriend for helping me.
I will keep looking for an all-encompassing formula.
Dan
download and activate the morefunc addin from
http://download.cnet.com/Morefunc/30...-10423159.html
then use
=EVAL(SUBSTITUTE(A1,",","+"))
or slightly more tricky
define a name
insert/name/define
call it say "result"
in refers to put
=EVALALUTE(SUBSTITUTE($A1,",","+"))
then with 1,2,3,20,50 in A1 in b1 put
=result
Last edited by martindwilson; 01-13-2010 at 04:53 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I'm getting things to work little by little. DonkeyOte, your plan worked.
I'm curious now, what if I want to substitute the numbers to letters.
Can I get A,B,C to show up in B1 as A+B+C.
Ultimately, I want the variables to refer to its value defined on another sheet and then C1 will actually express the numerical total.
Thank you!
If you're saying you have three named constants - say _A, _B, _C then
A1: _A,_B,_C
should still generate the appropriate output in B1 (ie the sum of those three constants), yes.
If you just want to display A,B,C as A+B+C, you could use part of what Martin postedWould that work for you?=SUBSTITUTE(A1,",","+")
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Is this what you want?
=SUBSTITUTE(A1,",","+")
How about this:
=SUM(--MID(SUBSTITUTE($A$1;",";0);ROW(A1:INDIRECT("A"&LEN(A1)));1))
Comfirmed with ctrl+shift+enter
(replace ; with , if needed)
Never use Merged Cells in Excel
Thank you all for thinking on this!
I was trying to figure out and try various solutions presented by you.
So what I would have in cell A1 would be A,B,C. ("A,B,C" is if there is just three. There could be less or more than 3, ie "A,B" or "A,B,C,D" etc.)
In B1, I would like to see however many letters are delimited in A1 to be shown as A+B+C, or if there were 4 letters show up in A1, then B1 would show A+B+C+D.
Then in C1, if I define under the "Insert Name" command that A will always be the value I enter in, say, E1, B will be the value I enter in F1, C will be the value I enter in G1, and D, will be the value I enter in H1, etc, then in the case of A=1, B=2, C=3, and D=4, I want to see the number 10 in C1.
Is this all possible with formulas?
I'm not sure I understand the need for B1 string...
If as advised you create Names _A, _B, _C etc... (you shouldn't / can't use C for ex.) such that _A refers to =$E$1, _B to $F$1 etc... then you can use what you already have ie:
... the only limitation to the x,y,z rather than x+y+z is that the former is constrained in terms of how many items you can include in the delimited list but I suspect that won't be an issue here, ie:A1: _A,_B,_C,_D B1: =Eval
is the same as=SUM(1,2,3)
(edit: in fact the SUM route has added advantage over + given the lack of explicit coercion taking place - ie non-numeric names won't generate #VALUE!)=1+2+3
Last edited by DonkeyOte; 01-14-2010 at 11:17 AM.
I have tried to put in some of the code but I can't get it to work for me. What am I doing wrong?
Let's assume I do indeed make A1 show the following:
_A,_B,_D (I'm going to skip "C" because I have no reason, but Excel does not allow it.)
Sometimes A1 will have _A,_B or even _D,_I or even _A,_B,_E,_F,_K.
I will go down the line starting in E1, then F1, etc to define that those cells are what determine what value A through K will have. Perhaps I need to say that those cells need to define what (underscore) _A through (underscore) _K are?
I can understand the thought that I don't need the B1 expression of _A+_B+_D etc. So I'll just skip to the SUM that I want and say that B1 will simply express the SUM of the values of the letters in A1.
Do I put =EVAL in B1? Is that a formula? Is EVAL a concept that involves addition?
To clarify, _A and all the other letters could have a two digit value, one digit, 3 digit, etc. I'd like the letters to accomodate any number size. And further, any number of letters could be shown delimited in the cell, so I will need to prepare by defining out as many letters as necessary.
Maybe I'm also not using the Name feature correctly.
Sorry for the confusion.
Dan
see attached ...
(use of _ in defined names is of course optional - I use it to prevent naming issues - eg C is not valid name whereas _C is...)
worth adding perhaps that the content of A cells need not be defined names - you could still use numerical expressions - eg 1,10,23 etc...
EDIT:
attachment reloaded at 2109 UK time...
Last edited by DonkeyOte; 01-14-2010 at 05:07 PM.
Thanks DonkeyOte for the sample sheet.
I can see now that I had not entered the EVAL formula in the Name insertions.
Thanks for your supporting information.
Will mark now as solved. I'll take to another level when I sort through what I need to do.
Thanks to everyone who helped.
Dan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks