Countif number or number WITH * asterisk - ignore other symbols or cells with text

[little_johnny]

Could someone help me with a formula to count cells that ONLY contain a number AND/OR a number with the " * " (asterisk) symbol?

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

 

[sadath]

Sorry, i assume you need the count,
here is the formula to count
CSE formula
=SUM(IFERROR(ISNUMBER(SEARCH("~*",A1:A20))*SUBSTITUTE(A1:A20,"*",""),0))

 

[hiker95]

sadath,

Nicely done - another one (in the same thread) for my archives - thanks.

 

[dispelthemyth]

The next logical step is to sum the numbers which can be done with the below (still an array - ctrl + Shift + Enter formula)

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

You genius- works perfectly. Thanks!

 

[dispelthemyth]

Just realised it can be shorted too, don't need the iferror

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

dispelthemyth,

Nicely done - one for my archives - thanks.

 

[martindwilson]

i suppose posts 5 and posts 9 which is essentially the same thing didnt work for some reason?

 

[dispelthemyth]

They work,

i suppose posts 5 and posts 9 which is essentially the same thing didnt work for some reason?