Hi all,
I'm a basic excel user really and cannot find a function that will allow me to complete the following:
CELL A1 includes the following sequence of numbers: 1,2,1,0,2,3
I would like to know how to put a formula/function into CELL B1 that sums up the sequence of numbers in CELL A1 (i.e 1+2+1+0+2+3)
Please help!!
Thanks
Maria
Welcome to the forum, Mazb.
This can be hacked, but it will be just that.
Why not put the numbers in separate columns? If you have a lot of them, you can do Data > Text to columns to separate them, then use a conventional SUM formula.
Entia non sunt multiplicanda sine necessitate
Thanks..I know I can do it that way, but its a last resort really. It would work much better for me if I could do it in one cell.
Do the values to be summed ever exceed 9 ?
If not then
B1:
=SUMPRODUCT((LEN($A1)-LEN(SUBSTITUTE($A1,{0,1,2,3,4,5,6,7,8,9},""))),{0,1,2,3,4,5,6,7,8,9})
A UDF alternative which should cater for all values, eg:
Called from cell B1: =SUMSTRING(A1,",")
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
=eval(substitute(a1,",","+")) should do it
"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
That is brilliant. The following formula worked perfectly.
=SUMPRODUCT((LEN($C5)-LEN(SUBSTITUTE($C5,{0,1,2,3,4,5,6,7,8,9},""))),{0,1,2,3,4,5,6,7,8,9})
glad you like long formulas then!
EVAL will work for any number not just single digits.
Martin, EVAL is part of morefunc.xll, ie not otherwise available.
For your alternative approach to work you would need to install 3rd party add-in and embed / or distribute as required... which may/may not be viable for OP.
yep and i dont know if it works on a mac either ! donkeyote 3 martin 0
In Excel 2003, I have used the following UDF (without morefunc.xll)
orPlease Login or Register to view this content.
BUT, no idea if this will work with Excel 2004 for the Mac as OP uses.Please Login or Register to view this content.
HTH ...spellbound
If Cell A1 will always contain single-digits separated by commas (,)
try this:
Please Login or Register to view this content.
But, if A1 will always contain six single-digit numbers separated by commas
try this:
Please Login or Register to view this content.
Ron, I like the 2nd one and as you know I would normally defer judgement to you but in this instance I would argue the first is no improvement on that offered already (post 4) - in fact perhaps it is less effective given usage of INDIRECT, no ?
For any number of digits, ex.:
1,23 , 456,2.4 ,-4
we can also use a named formula.
Ex. To sum the cell to the left
- select B1
- define the name:
Name: SumCSV
Refers to: =EVALUATE(SUBSTITUTE(Sheet1!A1,",","+"))
This named formula evaluates the cell to the left. Ex., in B10:
=SumCSV
evaluates A10.
For any number of digits, if you don't want to use vba, you can also use an array formula, extracting each number and summing them, but it will not be a small formula.
As you may know, I'm not a big fan of INDIRECT solutions.
INDIRECT isn't inherently evil, but when other people's large
workbooks get sluggish, they're one of the first things I look for.
In this particular case, I absentmindedly pulled an old formula from my stash.
I should have posted my usual caveat that when used
en masse, INDIRECT solutions degrade workbook performance.
Thanks for questioning me on that one. As soon as I read your comment,
I gave myself a newspaper over the snout!
I have a further query that I hope you can help with...
I still have the cell with a sequence of numbers between 0 and 3, for example:
(Cell A1) Player 1: 123102123122
(Cell A2) Player 2: 121000212321
I have 10 rows like this. For each player I want to count the number of times:
- they scored '0' (cell B1)
- they scored '1' (cell C1)
- they scored '2' (cell D1)
- they scored '3' (cell E1)
So, is there a formula that allows me to do this?
Thanks
I'm assuming Player 1 does not appear in the string (else the Sumproduct would fail -- ie 1 would be included in the total)
If so... use LEN
B1: =LEN($A1)-LEN(SUBSTITUTE($A1,0,""))
You can of course be creative re: incrementing integer
B1: =LEN($A1)-LEN(SUBSTITUTE($A1,COLUMNS($B1:B1)-1,""))
copied to C1:E1
That works a treat..just what I wanted. Thanks lots!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks