Countif

[tiredofit]

I have a column of data and I want to find how many semicolons are in that column.

So for example:

a;
b;
c
d;

I would expect to return 3.

However, for:

e;f;
g;
h;i;j;
k;

I expect to return 7.

I tried using:

=COUNTIF(A:A,";"))

but it only returned 4 for my second example.

Can someone please point me in the right direction?

Thanks


EDIT SORTED:

I found the answer:

=SUM((LEN(A:A)-LEN(SUBSTITUTE(A:A,";","")))/LEN(";"))

but can someone please explain what the formula is doing.

Thanks again.

 

[Fluff]

Your Countif returns 0 for me with the 2nd set of data.
Try
=SUMPRODUCT(--(LEN(A1:A4)))-SUMPRODUCT(--(LEN(SUBSTITUTE(A1:A4,";",""))))

 

[jmacleary]

Hi there. Try this:

<code>=SUMPRODUCT(LEN(A:A)-LEN(SUBSTITUTE(A:A, ";","")))</code>

 

[tiredofit]

Thanks to both.

I forgot to mention with my formula, I entered it as an array, ie Ctrl+Shift+Enter.

But I prefer using SUMPRODUCT,

 

[Fluff]

Glad we could help & thanks for the feedback

 

[RoryA]

but can someone please explain what the formula is doing.

For each row, it calculates the length of the cell contents minus the length after replacing all semicolons with nothing, which will give you the number of removed characters. The division part is unnecessary here, but would be required if you were looking for more than one character.

 

[tiredofit]

For each row, it calculates the length of the cell contents minus the length after replacing all semicolons with nothing, which will give you the number of removed characters. The division part is unnecessary here, but would be required if you were looking for more than one character.

Thanks for the explanation.

 

[James006]

Thanks to both.

I forgot to mention with my formula, I entered it as an array, ie Ctrl+Shift+Enter.

But I prefer using SUMPRODUCT,

For your info ... SUMPRODUCT is an Array Formula ... without the need for CSE ...

 

[tiredofit]

Thanks.