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

 

[dispelthemyth]

The below would also count *5 and * 6

<code>=SUM(--(ISNUMBER(IFERROR(SUBSTITUTE(IF(A1:A20="", "T", A1:A20),"*","")+0,"T"))))</code>

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]

=sumproduct(--isnumber(search("~*",a1:a20)))

 

[little_johnny]

You genius- works perfectly. Thanks!

The below would also count *5 and * 6

<code>=SUM(--(ISNUMBER(IFERROR(SUBSTITUTE(IF(A1:A20="", "T", A1:A20),"*","")+0,"T"))))</code>

 

[martindwilson]

=sumproduct(--(isnumber(substitute(a1:a20,"*","")+1)))

 

[little_johnny]

This is unfortunately only counting the numbers WITH an asterisk- it's not counting individual numbers

thanks anyway, dispelthemyth solved anyway

=sumproduct(--isnumber(search("~*",a1:a20)))

 

[dispelthemyth]

Sadath, your formula is only finding and counting the number of occurances of the *

i.e.BBB* is counted but 4 is not

=sumproduct(--isnumber(search("~*",a1:a20)))

 

[dispelthemyth]

no worries, anything to take my mind of real work or revision

You genius- works perfectly. Thanks!

 

[VBA Geek]

this would be

=SUMPRODUCT(ISNUMBER(SUBSTITUTE($O$1:$O$15;"*";"")+0)+0)

This is unfortunately only counting the numbers WITH an asterisk- it's not counting individual numbers

thanks anyway, dispelthemyth solved anyway

Originally Posted by sadath
=sumproduct(--isnumber(search("~*",a1:a20)))

 

[hiker95]

dispelthemyth,

Nicely done - one for my archives - thanks.