G
Guest
how do i take a 12 digit field, in 1 cell and add each digit to the next, to
get a total and put it into the next cell?
get a total and put it into the next cell?
B
Bob Phillips
If it is always a 12 digit number, then simply
=SUMPRODUCT(--(MID(A1,ROW($1:$12),1)))
If it may be more or less, a more generic solution is
=SUM(IF(ISNUMBER(--MID(A1,ROW($1:$256),1)),--MID(A1,ROW($1:$256),1),0))
which is an array formula, so commit with Ctrl-Shift-Enter
--
HTH
RP
(remove nothere from the email address if mailing direct)
=SUMPRODUCT(--(MID(A1,ROW($1:$12),1)))
If it may be more or less, a more generic solution is
=SUM(IF(ISNUMBER(--MID(A1,ROW($1:$256),1)),--MID(A1,ROW($1:$256),1),0))
which is an array formula, so commit with Ctrl-Shift-Enter
--
HTH
RP
(remove nothere from the email address if mailing direct)
B
Bob Phillips
Knew there was a simpler solution 
=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
regardless of size of A1
--
HTH
RP
(remove nothere from the email address if mailing direct)
=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
regardless of size of A1
--
HTH
RP
(remove nothere from the email address if mailing direct)