Forum Discussion

Niklas Kalz's avatar
Niklas Kalz
Copper Contributor
Oct 14, 2017
Solved

Counting numbers in a column

Hello together!

How is it possible to count the amount of numbers in a column:

The column of numbers look like this:

 

1,2

3,4

3,5

5

9

6

But: THE COMMAS in each row should NOT be counted, only the whole amount of numbers finally.

I have tried it but unfortunately it was not possible. Has someone an idea?

Many thanks in advance!!

Niklas

excel
Formulas and Functions
training
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Oct 14, 2017

    Hi,

     

    This is the formula that you need:

    =SUMPRODUCT(LEN(SUBSTITUTE(A1:A6,",",""))) 

    • Niklas Kalz's avatar
      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's avatar
        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

Resources