Read only the number value of a cell

Can you upload an example?

Not THE Pedro Collins ? It sounds like a cricket related question too...

You can count instances using COUNTA(A1:A100) which will count both numbers and text.

To count * you can as you say use: COUNTIF(A1:A100,"*")

To sum all values you could use for ex.

=SUMPRODUCT(--(SUBSTITUTE("0"&A1:A100&"*","*","")))

to count values with *

Please Login or Register  to view this content.

to count Unstarred values

Please Login or Register  to view this content.

to sum all values including *'d values

Please Login or Register  to view this content.

would have sugested countif but assumed * would be taken as a wildcard

Hi Squiggler, re: the summation... I agree with the SUBSTITUTE approach and you've shown I have a superfluous addition of * to the strings but so as to handle possibility of blanks it would be a good idea (I think) to append the string with "0" ... this way blanks become 0 and the coercion from string to number won't error.... so I would go with:

=SUMPRODUCT(--("0"&SUBSTITUTE(A1:A100,"*","")))

(all based on OP assumption that values are always either number or number * ... the 0 is to offer future capacity (ignoring DNR))

Originally Posted by squiggler47

would have sugested countif but assumed * would be taken as a wildcard

It is really, but it's signifying text requirement so the numbers are ignored... if you added say "a" to the range of values

=COUNTIF(A1:A100,"*")

would count it, it's not in that form really counting instances of * ... to do that you would precede the * with ~ so as to have it treated literally

=COUNTIF(A1:A100,"*~*")

so something* .. this would ignore the "a"

yes I would agree donkey, but the ambiguity of
countif(a1:a100,"*")

not being a wild card but

countif(a1:a100,"3*")

is is a little annoying!

and I would probably have done 0& rather than "0"& (just from lazyness!)

I am assuming our last posts crossed...

countif(a1:a100,"*")

the above is a wildcard approach, and

countif(a1:a100,"3*")

would obviously only count text strings beginning with 3.

To count strings containing an * in a literal sense you would use tilde

countif(a1:a100,"*~*")

thereby stating you're looking to count text strings that end with an asterisk.

attached is the example....

Sadly for me i am a mere Peter Collins, but it is cricket related!

the starred values are Not Outs.. and thus the value is used in the average but it is not "counted" when dividing through.

cheers

Pedro

ps.. i am row 8..

OK so adapting the examples provided thus far:

U3: =SUMPRODUCT(--(0&SUBSTITUTE(A3:R3,"*","")))
copy down

V3: =MAX(--(0&SUBSTITUTE(A3:R3,"*","")))
committed with CTRL + SHIFT + ENTER
copy down... note this won't denote * if MAX was unbeaten as was the case with your 1/2 century!

Y3: =IF(S3-T3,U3/(S3-T3),0)
copy down

You could also alter approach in S:

S3: =COUNTA(A3:R3)
copy down

And to avoid confusion...

T3: =COUNTIF(A3:R3,"*~*")
copy down