adding digits of a number

[Guest]

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.

 

[tony h]

=MID(A6,1,1)+MID(A6,2,1) and such like

regards

 

[Guest]

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))

 

[Lotus123]

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(A1,3,1))+VALUE(MID(A1,4,1))+VALUE(MID(A1,5,1))+VALUE(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]

wish I knew arrays better

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))

 

[Guest]

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))

 

[Bob Phillips]

=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)

 

[Guest]

Wow. This is better than searching through books or maybe even the MS Excel
help. Thanks guys.

 

[Bob Phillips]

Wow. This is better than searching through books or maybe even the MS Excel
help.

maybe ?? definitely <vbg>