Forum Discussion
SumLength Function
Hello everybody,
I would like to count the amount of numerics (1-10) in a row,
but without commas,
I have to take into account
that in one cell is more than one number like
1,2,4,7,9
8,9,4,2
2,3,10
the range of numerics is 1 - 10 where 10 shoud be counted as ONE numeric like
the others:
The result for this example would be:
1,2,4,7,9
8,9,4,2
2,3,10
Result: 12
I have counted the AMOUNT OF numerics WITHOUT COMMAS and the 10 like ONE numeric,
finally SUM it up: result -->12.
I have already tried it with several commands like "sum" and "length" etc. but unfortunately
I was not succesful.
I hope that some will find the time to suggest a solution to solve this problem.
Many thanks in advance,
cheers,
Niklas
Hi Niklas,
Actually your task is to calculate number of commas in the string. Let your first string is in A1
1,2,4,7,9
and the next are in A2, etc. Common formula to calculate number of some characters in the string is to compare its' length with and without such character:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
And we add +1 since numbers are one more than commas.
Next, you have to calculate sum of above counts for all your rows with such strings, other words do that on the array of cells. On of typical ways for that is to use SUMPRODUCT:
=SUMPRODUCT((LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,",",""))+1)*1)
What the sumproduct do is calculates the formula for each of A1:A3, multiplies result on 1 and after that sums all above.
Final result is 12.
4 Replies
Hi Niklas,
Actually your task is to calculate number of commas in the string. Let your first string is in A1
1,2,4,7,9
and the next are in A2, etc. Common formula to calculate number of some characters in the string is to compare its' length with and without such character:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1
And we add +1 since numbers are one more than commas.
Next, you have to calculate sum of above counts for all your rows with such strings, other words do that on the array of cells. On of typical ways for that is to use SUMPRODUCT:
=SUMPRODUCT((LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,",",""))+1)*1)
What the sumproduct do is calculates the formula for each of A1:A3, multiplies result on 1 and after that sums all above.
Final result is 12.
Hello Niklas
=MMULT({1,-1},LEN(SUBSTITUTE(TEXTJOIN(",",TRUE,A1:A3),{"";","},{"";""})))+1- Many thanks!!
Many thanks!!
