Forum Discussion

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, o...

excel

Formulas and Functions

training

Haytham Amairah
Oct 14, 2017
Hi,

This is the formula that you need:
=SUMPRODUCT(LEN(SUBSTITUTE(A1:A6,",","")))

Haytham Amairah

Silver Contributor

Oct 14, 2017

Hi,

This is the formula that you need:

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

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:

1,2,3

4,5,6

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!!