Problem with COUNT

[Cong Nguyen]

In cell B1: I wanted to count the number of commas (,) in cell A1

A, B, C, D, F, G, H 2

Please check what's wrong with my COUNT formula, COUNTA(A1,","), somehow the
result is always "2".

Thank you

 

[Gary''s Student]

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

 

[Darren Bartrup]

You're using the wrong formula.
COUNTA counts cells that are not empty - it doesn't count the occurences of
a character within a single cell.

This formula should do the trick:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

This counts how many characters are in cell A1, it then counts how many
characters are in cell A1 with the , removed.
Take one from the other and you have your result.

 

[Cong Nguyen]

Thank you Gary"s Student,
It works great.

 

[Cong Nguyen]

Dear Darren Bartrup,
Thank you very much for the expanation. Now it's clear.