tag:blogger.com,1999:blog-67692005342265499162011-04-21T20:35:25.685-07:00MS Excel TricksUmesh Khothttp://www.blogger.com/profile/18269267597913054307noreply@blogger.comBlogger11100tag:blogger.com,1999:blog-6769200534226549916.post-83071816983689033702011-03-22T05:27:00.000-07:002011-03-22T05:27:13.865-07:00Calculating Sum of Digits in a Number using Array Formulas<div dir="ltr" style="text-align: left;" trbidi="on"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;">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 58.</span><br /><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;"><br /></span><br /><h3 style="line-height: 140%;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">How to get the sum of digits?<o:p></o:p></span></h3><div><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;"><h3 style="line-height: 140%;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">Formula:<o:p></o:p></span></h3><div style="line-height: 140%;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">Assuming the number is in cell B4, we write the formula,<o:p></o:p></span></div><div style="line-height: 140%;"><code><span style="color: black; font-size: 10.0pt; line-height: 140%;">=SUMPRODUCT(MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0)</span></code><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;"><o:p></o:p></span></div><div style="line-height: 140%;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">to get the sum of digits.<o:p></o:p></span></div><div style="line-height: 140%;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">Note: you need <strong><i><span style="font-family: "Arial","sans-serif";">not </span></i></strong>press CTRL+SHIFT+Enter to get this formula work.<o:p></o:p></span></div><h3 style="line-height: 140%;"><span style="color: black; font-family: "Arial","sans-serif"; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">How does this formula work?<o:p></o:p></span></h3><div style="line-height: 140%;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">We will go inside out to understand this formula.<o:p></o:p></span></div><div style="line-height: 140%;"><strong><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">The portion – </span></strong><code><b><span style="color: black; font-size: 10.0pt; line-height: 140%;">ROW(OFFSET($A$1,,,LEN(B4)))</span></b></code><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">:<strong><span style="font-family: "Arial","sans-serif";"> </span></strong>Gives the numbers 1,2,3…n where n is the number of digits of the value in B4.<o:p></o:p></span></div><div style="line-height: 140%;"><strong><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">The portion – </span></strong><code><b><span style="color: black; font-size: 10.0pt; line-height: 140%;">MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)</span></b></code><strong><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">: </span></strong><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">Now gets the individual digits of the number and returns them as array (since the 2nd argument of MID formula is an array.<o:p></o:p></span></div><div style="line-height: 140%;"><strong><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">The SUMPRODUCT: </span></strong><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 140%;">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.<o:p></o:p></span></div></span></div></div><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/6769200534226549916-8307181698368903370?l=exceltricks.blogspot.com' alt='' /></div>Umesh Khothttp://www.blogger.com/profile/18269267597913054307noreply@blogger.com0