Forum Discussion
Solved
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,...
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!!