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!!

 

[dispelthemyth]

Below now ignores blanks

=SUM(--(ISTEXT(IFERROR(SUBSTITUTE(IF(A1:A20="", 1, A1:A20),"*",1)+0,"T"))))

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)

 

[little_johnny]

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)))

BINGO! worked perfectly.

Thanks mate

 

[little_johnny]

Below now ignores blanks

=SUM(--(ISTEXT(IFERROR(SUBSTITUTE(IF(A1:A20="", 1, A1:A20),"*",1)+0,"T"))))

Ok thanks mate! this works great too!

Just a quick note- I played around with some variations (out of my original description, just to test it), and your formula will also count cells that BEGIN with an * and have a space followed by a number (i'm guessing it counts the space after "*"?)

Whereas Aladin's formula only counts text based cells, and ignores number based or number and * based or number, space and * based cells.

eg: Aladin's formula will not count * 1 or ** 3, whereas yours will.

For the purposes of my original problem, both your formulas work, so thank you both

 

[little_johnny]

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)))

Below now ignores blanks


=SUM(--(ISTEXT(IFERROR(SUBSTITUTE(IF(A1:A20="", 1, A1:A20),"*",1)+0,"T"))))

Could someone help me with a formula to count cells that ONLY contain a number AND/OR a number with the " * " (asterisk) symbol? So kind of opposite to my previous question...


I wish to ignore other symbols, as well as cells which contain text. It must also ignore blanks.


Eg:


AAA
AAA
AAA
1
2
3*
BBB
BBB
BBB*
BBB**
1
2
3
4
5**


In the above example, I would expect the formula to give me an answer of 8 (includes the 3* and 5**), and ignore the cells with text (even if they contain an asterisk, such as BBB* and BBB**).


Thoughts?

 

[Aladin Akyurek]

Could someone help me with a formula to count cells that ONLY contain a number AND/OR a number with the " * " (asterisk) symbol? So kind of opposite to my previous question...


I wish to ignore other symbols, as well as cells which contain text. It must also ignore blanks.


Eg:


AAA
AAA
AAA
1
2
3*
BBB
BBB
BBB*
BBB**
1
2
3
4
5**


In the above example, I would expect the formula to give me an answer of 8 (includes the 3* and 5**), and ignore the cells with text (even if they contain an asterisk, such as BBB* and BBB**).


Thoughts?

Control+shift+enter:

=SUM(IF(ISNUMBER(SUBSTITUTE(A2:A16,"*","")+0),1))

 

[Idz21]

I wanted to piggy back off this thread, as the questions & formulas given here are in line with what I'm trying to do. I want to do a count of the entire A:A column, and only ignore blank cells and those that have an asterisk * in them. I'm doing the entire A:A, instead of a range, because the contents will continually change. I just want to always be able to tell how many total rows have actual data I care about, excluding the sub-topics that have an * in them.

 

[Aladin Akyurek]

I wanted to piggy back off this thread, as the questions & formulas given here are in line with what I'm trying to do. I want to do a count of the entire A:A column, and only ignore blank cells and those that have an asterisk * in them. I'm doing the entire A:A, instead of a range, because the contents will continually change. I just want to always be able to tell how many total rows have actual data I care about, excluding the sub-topics that have an * in them.

Which formula of the thread fits your question exactly?

 

[Idz21]

Which formula of the thread fits your question exactly?

None of them fit exactly, since the prior formulas are asking to exclude data with alphanumeric characters. I wasn't able to figure out how to edit the existing formulas to make that determination. I need a COUNTIF that'll only exclude cells that have an * or are completely blank. Everything else should be counted. My data has both numeric & alphanumeric values.

 

[Aladin Akyurek]

None of them fit exactly, since the prior formulas are asking to exclude data with alphanumeric characters. I wasn't able to figure out how to edit the existing formulas to make that determination. I need a COUNTIF that'll only exclude cells that have an * or are completely blank. Everything else should be counted. My data has both numeric & alphanumeric values.

Try...

=COUNT(A:A)+COUNTIF(A:A,"?*")-SUMPRODUCT(ISNUMBER(FIND("*",A:A))+0)

 

[Idz21]

Try...

=COUNT(A:A)+COUNTIF(A:A,"?*")-SUMPRODUCT(ISNUMBER(FIND("*",A:A))+0)

That did it. Thank you sir!