Home 
Search 
Today's Posts 
#1




adding digits of a number
Hello.
Does anyone knows a function or formula that would add the digits of a number and spit out a result? Example.... take the number 1092836103274 (all by itself in a cell) add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30 or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16 curious dantee. 
#2




adding digits of a number
=MID(A6,1,1)+MID(A6,2,1) and such like regards  tony h  tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=527838 
#3




adding digits of a number
Assume cell A1, need to enter these formulas with CTRL+SHIFT+ENTER as they
are array formulas: To add the odd numbers: =SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0)) To add the even numbers: =SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 0,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))  Regards, Dave "dantee" wrote: Hello. Does anyone knows a function or formula that would add the digits of a number and spit out a result? Example.... take the number 1092836103274 (all by itself in a cell) add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30 or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16 curious dantee. 
#4




adding digits of a number
Several ways to approach this...depending on how complex the data series is. I'll assume the data series is uniform for this example...aka, every number you want to use this one will have 6 digits. Number is in cell A1..formula in A2. =VALUE(MID(A1,1,1))+VALUE(MID(A1,2,1))+VALUE(MID(A 1,3,1))+VALUE(MID(A1,4,1))+VALUE(MID(A1,5,1))+VALU E(MID(A1,6,1)) This formula could be expanded for a larger number by adding to the end. If you attempt to use this formula on a number smaller than 6 digits you will get an error...which gets into uniformity. If your data is different, you can also add a check on each MID to see the value is there, ISERROR()...if it is an error, don't add it.  Lotus123  Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611 View this thread: http://www.excelforum.com/showthread...hreadid=527838 
#5




adding digits of a number
wish I knew arrays better David Billigmeier Wrote: Assume cell A1, need to enter these formulas with CTRL+SHIFT+ENTER as they are array formulas: To add the odd numbers: =SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0)) To add the even numbers: =SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 0,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))  Regards, Dave  Lotus123  Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611 View this thread: http://www.excelforum.com/showthread...hreadid=527838 
#6




adding digits of a number
Slight update, you don't have to enter these with CTRL+SHIFT+ENTER. Also
note my formulas don't depend on the length of your number, you can have as long or as short a number as you would like: Even positions: =SUMPRODUCT((MOD(ROW(INDIRECT("1:"&LEN(A1))),2)=0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) Odd positions: =SUMPRODUCT((MOD(ROW(INDIRECT("1:"&LEN(A1))),2)=1),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))  Regards, Dave "David Billigmeier" wrote: Assume cell A1, need to enter these formulas with CTRL+SHIFT+ENTER as they are array formulas: To add the odd numbers: =SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0)) To add the even numbers: =SUMPRODUCT(IF(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)= 0,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))  Regards, Dave "dantee" wrote: Hello. Does anyone knows a function or formula that would add the digits of a number and spit out a result? Example.... take the number 1092836103274 (all by itself in a cell) add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30 or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16 curious dantee. 
#7




adding digits of a number
=SUMPRODUCT((MID(A11,{1,3,5,7,9,11,13},1)))
Just change {1,2,3,5,7,9,11} to each digit that you want to count  HTH Bob Phillips (remove nothere from the email address if mailing direct) "dantee" wrote in message ... Hello. Does anyone knows a function or formula that would add the digits of a number and spit out a result? Example.... take the number 1092836103274 (all by itself in a cell) add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30 or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16 curious dantee. 
#8




adding digits of a number
Wow. This is better than searching through books or maybe even the MS Excel
help. Thanks guys. Earlier today "dantee" wrote: Hello. Does anyone knows a function or formula that would add the digits of a number and spit out a result? Example.... take the number 1092836103274 (all by itself in a cell) add the 1st, 3rd, 5th digits and so on to get 1+9+8+6+0+2+4=30 or add the 2nd, 4th, 6th digits and get the result of 0+2+3+1+3+7=16 curious dantee. 
#9




adding digits of a number
"dantee" wrote in message ... Wow. This is better than searching through books or maybe even the MS Excel help. maybe ?? definitely <vbg 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Adding digits in a single cell  Excel Worksheet Functions  
calculate using last four digits of number in cell  Excel Worksheet Functions  
Adding a 0 to a four digit number  Excel Discussion (Misc queries)  
How to set a column to accept only a specified number of digits  Excel Worksheet Functions  
Least number of digits in Yaxis labels  Charts and Charting in Excel 