Here is a fun formula to write.
Given a number in cell, I want you to find the sum of digits in it. So, for eg. if you have the number 3584398594 in a cell, the sum would be =3+5+8+4+3+9+8+5+9+4, equal to 1994. 😛
I am kidding of course, the sum would be 58.
Now, how would you write a formula to find this sum automatically based on the number entered in the cell?
Go ahead and figure it out. If you can, come back and check your answer with mine below.
How to get the sum of digits?
In order to get the sum of digits, we just need to separate and add all the numbers. Sounds simple right? But how!?!
Very simple, we use Array formulas and pixie dust.
First the formula:
Assuming the number is in cell B4, we write the formula,
=SUMPRODUCT(MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0)
to get the sum of digits.
Note: you need not press CTRL+SHIFT+Enter to get this formula work.
How does this formula work?
We will go inside out to understand this formula.
The portion – ROW(OFFSET($A$1,,,LEN(B4))): Gives the numbers 1,2,3…n where n is the number of digits of the value in B4.
The portion – MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1): Now gets the individual digits of the number and returns them as array (since the 2nd argument of MID formula is an array.
The SUMPRODUCT: is the pixie dust. It just magically sums up all the digits extracted by MID(). We use a +0 at the end because MID() returns text that needs to be converted to numbers for SUMPRODUCT to work its magic.
How would you have solved this?
I just love SUMPRODUCT Formula. So I use it whenever I can. But you may like other techniques. So please tell me how you would solve this problem using formulas. Post your formula using comments.
Note: while posting your formula, just put it between CODE tags like this:
<code>your formula goes here</code> so that it gets displayed correctly.
Bonus question: How to calculate single digit sum of the digits?
Go ahead and solve it too.
The single digit sum is arrived by summing the sum of digits of sum of digits of … of a number. For ex. the single digit sum for number 3584398594 is 4 (because the sum of digits is 58, whose sum of digits is 5+8 = 13, whose sum of digits is 1+3 =4 and we stop here because 4 is a single digit number).
11 Responses to “Use Alt+Enter to get multiple lines in a cell [spreadcheats]”
@Chandoo:
One more useful trick.......
In a column you have no. of data in rows and need to copy in the next row from the previous row, no need to go for the previous rows but entering Alt + down arrow, you will get the list of data, (in asending order), entered in the previous rows...
This is another great tip. I use this all the time to make sense of some *very* long formulas. As soon as the formula is debugged I remove the break.
Great tip Chandoo!
I use this feature often and it has even gotten the, "how did you do that" response.
Thanks!
@Ketan: Alt+down arrow is an awesome tip. I never knew it and now I am using it everyday.
@Jorge, Tony: Agree... 🙂
[...] Day 1: Insert Line Breaks in a Cell [...]
how can we merge a two sheet.
excellent idea. Chandoo you are genious
Hi chandoo,
I have used ctrl+enter to break the cell. But I did not get the result.
Please tell me how can i break the cell in multiple lines.
Hi, Ranveer,
Its not Ctrl+enter to break the cell, use Alt+Enter to make it happen.
hi Chandoo....
how we can use Alt+Enter in multiple rows at the same time please reply hurry i have lot of work and have no time and i m stuck in this. 🙁
Alt+J worked once 🙁
So I found another more reliable way:
=SUBSTITUTE(A2,CHAR(13),"")
Where A2 is the cell that contains the line breaks which the code for it is CHAR(13). It will replace it with whatever inside the ""