in excel, I want to count the number of cells that do not contain a specific character

1
votes

in excel, I want to count the number of cells that do not contain a specific character (in this case, a "." /period).

I tried something like countif(A1:A10,"<>.*") but this is wrong and I can't seem to figure it out.

Say I have these data in column A:

D
N
P
.
.
A
N
.
P
.

And the count would be 6

3
excel

3 Answers

1
votes

For your example: 

=COUNTIF(A1:A10,"<>.")

returns 6. But it would be a different story if say you wanted to exclude P. from the count also.

Your data may not be quite what you think it is however, because including the * should make no difference for your example.

1
votes

Or you could subtract periods from the total and be left with the non periods

=COUNTIF(A1:A10,"*")-COUNTIF(A1:A10,"=.")

gives 6.

1
votes

If your data includes periods along with other characters in the same cell and want a similar count:

image

then this:

=COUNTA(A1:A10)-COUNTIF(A1:A10,"*.*")

will return 5