Today we are going to discuss about various methods in excel to sum all digits.
Lot to learn, let's dive in.
Source data resides in the B3:B11.
Our requirement is to extract each digit and add them using formula.
Method-1:
Office 365 version - Single Cell Formula.
Formula in C4 cell,
=MAP(B4:B11,LAMBDA(v,SUM(0+MID(v,SEQUENCE(LEN(v)),1))))
Syntax of MAP function,
=MAP(array,lambda_or_array2,...)
This is one of the LAMBDA function, which will do the given custom function on each iteration of array and will deliver each result.
In our case, we used variable 'v' in LAMBDA function,
You can use any names based on your requirement but basic naming conventions need to be followed.
'v' is current value in array for each iteration.
Let me take iteration of B4 cell,
Len(v) delivers the Length of B4 cell value which is 7,
If we wrap this inside SEQUENCE function, it will deliver the sequence of numbers 1 to 7.
Then MID function helps us to extract each digits, as of now output will look like,
={"1";"1";"1";"7";"2";"4";"5"}
Since, these are all texts, we need to convert this into numbers to add them.
Do some mathematical operation and these texts will become numbers. So, we add zero here. We will see different types in converting the text numbers into numbers in each method.
SUM function simply adds the numbers and will give us 21 in first iteration and it does the same computation for all iterations.
Method-2:
Office 365 version - Single Cell Formula.
=SCAN(0,B4:B11,LAMBDA(a,v,SUM(MID(v,SEQUENCE(LEN(v)),1)/1)))
Here, we used SCAN function to accomplish the task, this is also one of the LAMBDA function, but it will deliver the results of each iteration.
Here, to convert text numbers into numbers we divide it by 1.
Method-3:
Excel 2019 Version - Need to drag the formula across the range.
=SUM(--MID(B4,SEQUENCE(LEN(B4)),1))
Here, we put double negative before MID, this is considered to be the fastest method to convert text numbers into numbers.
Method-4:
Legacy version - Need to drag the formula across the range.
=SUMPRODUCT(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)*1)
Here, we used ROW/INDIRECT construction instead of sequence function.
INDIRECT function is volatile and can slow your spreadsheet with large data set.
Here, we multiply text numbers with 1 to convert them into numbers.
Method-5:
Legacy version - Need to drag the formula across the range.
=SUMPRODUCT((LEN(B4)-LEN(SUBSTITUTE(B4,ROW($1:$9),"")))*ROW($1:$9))
This is bit longer compared to Method-4, but efficient works well with alphanumeric strings too. This will not involve INDIRECT function.
Let me start this from scratch,
=SUBSTITUTE(B4,ROW($1:$9),"")
This will deliver 9 results, in first result, this will remove all 1's in B4 cell,
in 2nd result, this will remove all 2's in B4 cell and so on.
={"7245";"111745";"1117245";"111725";"111724";"1117245";"111245";"1117245";"1117245"}
If we wrap this in LEN function, it will deliver us the length of strings,
={4;6;7;6;6;7;6;7;7}
Subtract this from Overall length,
={3;1;0;1;1;0;1;0;0}
So, 1's are there in 3 occurrence,
2's are there in 1 occurrence and so on.
If we multiply this with 1 to 9 sequence,
={3;1;0;1;1;0;1;0;0}*{1;2;3;4;5;6;7;8;9}
Output will look like,
={3;2;0;4;5;0;7;0;0}
Sum of these numbers gives 21.
Method-6:
Power Query solution,
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SumOfDigits = Table.AddColumn(Source, "Sum of Digits", each List.Sum( List.Transform( Text.ToList( Text.From([Digits])), Number.From)))
in
SumOfDigits
Source is our data with numbers to be added,
in SumOfDigits step,
Text.From([Digits]) - helps to convert the numbers into text,
Text.ToList( ) - helps us to extract each digit in list,
1117245 will become {"1";"1";"1";"7";"2";"4";"5"},
List. Transform helps us to convert the texts into numbers using Number. From function,
List. Sum will add the numbers.
Method-7:
Power Query solution,
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SumOfDigits = Table.AddColumn(Source, "Sum Of Digits", each List.Accumulate( Text.ToList( Text.From([Digits])),0,(s,c) => s + Number.From(c)))
in
SumOfDigits
This is same as method-6 but with List. Accumulate.
={"1";"1";"1";"7";"2";"4";"5"}
This is the list where we start to do accumulation,
In 1st iteration,
Our seed is 0, this is going to be 's' (State),
So, state is zero and 'c' (Current) is "1",
Since our 'c' is text, we convert this into number using Number. From, we simply add this with 0, result of 1st iteration will be 1,
In the 2nd iteration,
's' is 1 and c is "1",
Convert our 'c' into number and adding this with 1 will give us 2,
if we do this for 7 iteration, our final result will be 21.
Kommentit