Forum Discussion
Finding Excel Formel Matrix
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
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
Many, many thanks, PERFECT!!
Best wishes from Hamburg (Germany),
Niklas
- And yes, everything what is between two commas is considered as one number (10, etc.)