ExcelBanter - Problem with COUNT

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Problem with COUNT (https://www.excelbanter.com/excel-discussion-misc-queries/171348-problem-count.html)

Cong Nguyen

Problem with COUNT

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

--
Cong Nguyen

Gary''s Student

Problem with COUNT

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
--
Gary''s Student - gsnu200762

Darren Bartrup[_2_]

Problem with COUNT

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" wrote:

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

--
Cong Nguyen

Cong Nguyen

Problem with COUNT

Thank you Gary"s Student,
It works great.
--
Cong Nguyen

"Cong Nguyen" wrote:

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

--
Cong Nguyen

Cong Nguyen

Problem with COUNT

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

"Darren Bartrup" wrote:

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" wrote:

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

--
Cong Nguyen

All times are GMT +1. The time now is 02:38 PM.