Countif but ignore numbers AND specific character - the asterisk * symbol

[little_johnny]

Hello,

I am trying to count a range of mixed cells. I use the "*" symbol in my cells to add emphasis to the cell when I am looking through them.

I only want to count the cells that contain TEXT characters (representing names) - but I do not want to count cells with numbers only OR numbers + the asterisk symbol...

As you can see below, some cells contain text only, some contain numbers only, and some contain numbers OR text proceeded with an " * " or " ** " symbol.

If they contain a number, or a number with an asterix, I do not want them to be counted...

I only wish to count the cells that contain any text (essentially a person's name).


Eg:

[TABLE="class: outer_border, width: 90, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4*[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jane**[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3**[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Lucy[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Lucy[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Lucy[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]2*[/TD]
[/TR]
</tbody>[/TABLE]

In this example, I want the answer to be 10 (the 10 names). I put the formula =COUNTIF(A$1:A$20,"*"), but it gives me 13.​

I understand "*" is used as a wildcard to count text, but I wish to ignore the 4*, 3**, 2*. Any help?

























Please note: this example is for illustrative purposes. My cells range in the hundreds, and a cell with a number proceeded with an asterisk (or double asterisk) could be very random (e.g.: 24*, 62*, 158**), so I can't ignore specific number AND "*", if that makes sense..?

Thanks!!

 

[Aladin Akyurek]

That did it. Thank you sir!

You are welcome. Thanks for the update.