Want to understand the below formulae

[uttamkumar1234]

HI,

PLEASE EXPLAIN ME THE BELOW MENTIONED FORMULAE WITH EXAMPLE.

SUMPRODUCT(--MID(H13,ROW(INDIRECT("1:" & LEN(H13))),1))

<tbody>
</tbody>

THANKS.

 

[Special-K99]

It's taking a number and adding up all the digits, e.g. 198732 produces 30 1+9+8+7...

It extracts each digit in H13 and using an array adds them up.
INDIRECT("1:"&LEN(H13)) produces a count 1,2,3,4,5 etc up to the length of the number. Each digit is then placed into the MID(H13...,1) to extract the 1st 2nd 3rd etc character
SUMPRODUCT then adds them up

 

[Aladin Akyurek]

HI,

PLEASE EXPLAIN ME THE BELOW MENTIONED FORMULAE WITH EXAMPLE.

SUMPRODUCT(--MID(H13,ROW(INDIRECT("1:" & LEN(H13))),1))

<tbody>
</tbody>

THANKS.

It sums up the digits of a number.

01. MID is used to extract each digit...

Let H13 = 26.

MID(H13,2,1)

>> 6

That is, the digit at position 2.

02. ROW function can be used to produce a sequence like {1,2}...

ROW(INDIRECT("1:"&LEN(H13))

>>

ROW(INDIRECT("1:2"))

>>

{1,2}

03. SumProduct like Sum can sum a set of numbers from a range or an array


04. From above, we get using...

SUMPRODUCT(--MID(H13,ROW(INDIRECT("1:" & LEN(H13))),1))

>>

SUMPRODUCT(--MID(H13,ROW(INDIRECT("1:" &2)),1))

>>

SUMPRODUCT(--MID(H13,ROW(INDIRECT("1:2")),1))

>>

SUMPRODUCT(--MID(H13,{1;2},1))

>>

SUMPRODUCT(--MID(21,{1;2},1))

>>

SUMPRODUCT(--{"2";"1"})

>>

SUMPRODUCT({2;1})

>> 3

which is the sum of the digits of the numeric string of H13. Hope this helps.