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:

	A
1	Bob
2	Bob
3	Bob
4	1
5	2
6	3
7	4*
8	Jane
9	Jane
10	Jane
11	Jane**
12	1
13	2
14	3**
15	Lucy
16	Lucy
17	Lucy
18	1
19	2*

<tbody>
</tbody>

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]

Control+shift+enter, not just enter:

=SUM(IF(LEN(A1:A19),IF(1-ISNUMBER(A1:A19),IF(1-ISNUMBER(FIND("*",A1:A19)),1))))

 

[dispelthemyth]

Ctrl + Shift + Enter the below formula
=SUM(--(ISTEXT(IFERROR(SUBSTITUTE(A1:A19,"*",1)+0,"T"))))

 

[little_johnny]

Great - this one works to ignore the numbers AND the asterisk!

BUT- problem now. It is ignoring any cell with an asterisk. Some of my names will contain an asterisk (eg: Jane** is not being counted). Is there a way to make it count just cells with text, and ignore only individually numbered cells and numbered cells that have an asterisk?

 

[little_johnny]

Control+shift+enter, not just enter:

=SUM(IF(LEN(A1:A19),IF(1-ISNUMBER(A1:A19),IF(1-ISNUMBER(FIND("*",A1:A19)),1))))

Great - this one works to ignore the numbers AND the asterisk!

BUT- problem now. It is ignoring any cell with an asterisk. Some of my names will contain an asterisk (eg: Jane** is not being counted). Is there a way to make it count just cells with text, and ignore only individually numbered cells and numbered cells that have an asterisk?

 

[dispelthemyth]

Post #3 works as you expect
eg Jane** is counted but 4* is not

Great - this one works to ignore the numbers AND the asterisk!

BUT- problem now. It is ignoring any cell with an asterisk. Some of my names will contain an asterisk (eg: Jane** is not being counted). Is there a way to make it count just cells with text, and ignore only individually numbered cells and numbered cells that have an asterisk?

 

[little_johnny]

Ctrl + Shift + Enter the below formula
=SUM(--(ISTEXT(IFERROR(SUBSTITUTE(A1:A19,"*",1)+0,"T"))))

Sorry I didn't see your reply and had already started replying to Aladin!

Ok this is working! Thank you - but I did not account for blank cells, which are also being counted, so I now have a new problem...

So to rephrase: ignore blank cells, ignore numbered cells, and ignore numbered cells with an asterisk/s - only count cells with text or text and asterisk

 

[Aladin Akyurek]

Great - this one works to ignore the numbers AND the asterisk!

BUT- problem now. It is ignoring any cell with an asterisk. Some of my names will contain an asterisk (eg: Jane** is not being counted). Is there a way to make it count just cells with text, and ignore only individually numbered cells and numbered cells that have an asterisk?

I thought you didn't want Jane with stars...

Control+shift+enter, not just enter:

=SUM(IF(1-ISNUMBER(SUBSTITUTE(A1:A19,"*","")+0),1))

if there are no formula blanks (i.e., ""). Otherwise:

=SUM(IF(1-(A1:A19=""),IF(1-ISNUMBER(SUBSTITUTE(A1:A19,"*","")+0),1)))

 

[little_johnny]

I thought you didn't want Jane with stars...

Control+shift+enter, not just enter:

=SUM(IF(1-ISNUMBER(SUBSTITUTE(A1:A19,"*","")+0),1))

Thanks for the reply Aladin, but as per my other reply to dispelthemyth - i also need to ignore blank cells (your formula also counts them)

 

[Aladin Akyurek]

Thanks for the reply Aladin, but as per my other reply to dispelthemyth - i also need to ignore blank cells (your formula also counts them)

Try again. The reply has a second formula to cover that case:

=SUM(IF(1-(A1:A19=""),IF(1-ISNUMBER(SUBSTITUTE(A1:A19,"*","")+0),1)))