Forum Discussion
Solved
Finding Excel Formel Matrix
Hello everybody,
I try to create an Excel- Formel
with which I can count the amount of numerics in a column,
but without the commas , and a number like 10 should only be counted once,
so for example :
1,2,10
1,7
the final result should be 5 (without commas and numbers like 10 (11-->two numerics ) should be counted as one.
Perhaps with "sum", "lenght" and "change" command/Matrix?
Has someone an idea?
Many thanks in advance!!
I really hope to hear from someone with due to this issue!!
Niklas
Niklas, assuming all your strings with numbers are in column A and you have nothing more in that column, you may use
=SUMPRODUCT(LEN(A:A)) - SUMPRODUCT(LEN(SUBSTITUTE(A:A,CHAR(44),""))) + COUNTA(A:A)
For your example with strings in A1:A3 it returns 12.
SUMPRODUCT sums lengths of cells which it considers as array. At the end we shall add 1 for each cell with text, do that by COUNTA which calculates number of non empty cells.
Sure, you may use concrete range instead of A:A, e.g A1:A20
Hi Niklas,
Not sure i understood how your data structured, but the idea is to compare length of the string with and without commas.
If, for example, in cell A1 you have the string
1,2,10,1,7
when formula
=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(44),""))+1
returns 5
Many thanks, Sergei, that helped,
in addition to this my intention is to add and count more than only one cell, more abstract,
in each cell there are several sequences of numericals like:
in one row:
1,2,10,1,7 (5 numericals)
1,3,4,17 (4 numericals)
5,6,9 (3 numericals)
The sum of length should be in this example finally 12 for the whole column "A".
-->For example three cells in the same column A1:A3
=SUM(LEN(SUBSTITUTE(A1:A3;",";"")-LEN(SUBSTITUTE(A1:A3,CHAR(44),""))+1
I still don´t know whether the two conditions (1. "don´t count the commas", 2. "if there is 10 etc. only
count as one! numerical and not as two) are fulfilled with my Formula?
Many thanks,
best,
Niklas
Niklas, assuming all your strings with numbers are in column A and you have nothing more in that column, you may use
=SUMPRODUCT(LEN(A:A)) - SUMPRODUCT(LEN(SUBSTITUTE(A:A,CHAR(44),""))) + COUNTA(A:A)
For your example with strings in A1:A3 it returns 12.
SUMPRODUCT sums lengths of cells which it considers as array. At the end we shall add 1 for each cell with text, do that by COUNTA which calculates number of non empty cells.
Sure, you may use concrete range instead of A:A, e.g A1:A20
