-
Sum Of The Digits Of Numeric Value In A Cell Range
-
Give this formula a try...
=SUMPRODUCT(--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))
-
Thank you very much.
It works. Can you explain use of "MID" within SUMPRODUCT or be kind enough to evaluate the formula?%O
-
Quote:
Originally Posted by
Zaigham
Thank you very much.
It works. Can you explain use of "MID" within SUMPRODUCT or be kind enough to evaluate the formula?%O
First off, we note that SUMPRODUCT is an array processing function... give it a range of cells and it performs the indicated "calculation" by iterating the range one cell at a time and summing the results of each of those iterations to produce its return value. The MID function returns a substring from within a larger text string. It has 3 arguments... the first is the larger text string, the second is a starting point within that larger text string from which the substring will be acquired and the third is how many characters (from that starting point) to take. So, we need to create an range (for the SUMPRODUCT function to process) which iterates through each digit of the number in A1 (first argument for MID) one digit at a time (third argument for MID)... that range of starting points from which to draw one digit at a time is what the second argument for the MID function is providing. The INDIRECT function creates a Range in Column A from Row 1 to the row number corresponding to the length of the number in A1... that range is the argument for the ROW function which is what SUMPRODUCT will iterate... so for the number 12546, the SUMPRODUCT function sees ROW(A1:A5) and when SUMPRODUCT iterates this range, it pulls out the numbers 1, 2, 3, 4 and 5... each of those is the starting point from whch 1 (third argument for MID) digit will be returned from the number in A1 for SUMPRODUCT to total up. The double minus sign is equivalent to multiplying each value returned by the iteration through the range in the second argument by +1 which doesn't change any values but, in Excel formulas, performing a math operation (multiply by +1 being such an operation) on a "text number" converts it from being text to being a real number... we do this so SUMPRODUCT has real numbers to sum up.
-
Mr. Rick
I am very very thankful to you for devoting your precious time for me. You have explained the method in detail that would guide me in future. I have found you very kind before this on another forum. Thank you very much again.:thumbsup:
-
Quote:
Originally Posted by
Zaigham
Mr. Rick
I am very very thankful to you for devoting your precious time for me. You have explained the method in detail that would guide me in future. I have found you very kind before this on another forum. Thank you very much again.:thumbsup:
My pleasure... and you are quite welcome... I am glad I have been able to be of help to you (both in this forum and the other one you referred to).
-
Another way...
Code:
=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})
Should also work if the cell mixed with text & numbers.
-
alternative; a UDF
e.g. in B5:
=snb_001(A5)
Code:
Function snb_001(y)
snb_001 = Evaluate(Replace(StrConv(y, 64), Chr(0), "+") & "0")
End Function
-
It is just amazing for me, as I was not sure for a single solution.
Thank you very much.
