Counting numbers in a column

Niklas Kalz
Copper Contributor
Oct 18, 2017
Hello Haytham,
unfortunately there still is one problem with this task and the function,
the range of my numbers go from 1 to 10 (1,2,3....10), your formular which is
a really good start, thanks again, only takes into account numbers from 1 to 9,
if there is a 10 this number is counted as two variables with your given function but
it should only be counted as one. I should have said this before, sorry!
Hope you can find the time?!
Many thanks!!
New example:
10
1,2,3
4,5,6
9
Result: 8
- Haytham Amairah
  Silver Contributor
  Oct 18, 2017
  Hi,
  
  Sorry about that bug in my formula!
  This is the fix:
  
  =COUNT(VALUE(A1:A5))+SUMPRODUCT(LEN(SUBSTITUTE(IF(NOT(ISNUMBER(VALUE(A1:A5))),A1:A5,""),",","")))
  But you have to press (Ctrl Shift Enter) to force the formula to give the right result.
  
  However, you can use the formula provided by Sergei, if you want less complicated solution.
  
  Regards
  - Niklas Kalz
    Copper Contributor
    Oct 19, 2017
    Thanks for your support, (I used the one of Sergei).
- SergeiBaklan
  Diamond Contributor
  Oct 18, 2017
  That could be like
  =SUMPRODUCT(LEN(A1:A7)-LEN(SUBSTITUTE(A1:A7, ",",""))+(LEN(A1:A7)>0))
  - Niklas Kalz
    Copper Contributor
    Oct 19, 2017
    Many thanks!! That is it!!
Niklas Kalz
Copper Contributor
Oct 17, 2017
Many thanks, this was the best answer!!

Forum Discussion