Is it possible to read numbers stored as text with a formula?

[excelos]

Hello!

Is there a formula that will read the numbers from a column that contains numbers but stored as text?

Thanks!

 

[Joe4]

So, are you saying that you cannot fix the format of column A?
Please explain the condition that you want to use for the COUNTIFS?

 

[excelos]

So, are you saying that you cannot fix the format of column A?
Please explain the condition that you want to use for the COUNTIFS?

Yes, I want to avoid fixing the format of column A. I want to do it in a formula if possible.

The COUNTIFS is just a >65 condition.

 

[XOR LX]

It's surprising how difficult it is to construct the equivalent COUNTIF(S) construction to:

=SUMPRODUCT(N(0+A1:A100>65))

The best I can come up with is:

=SUM(COUNTIFS(A:A,REPT("?",{0,1}+LEN(65))&{"","*"},A:A,">'"&{65,0}))

(replace the two instances of 65 as desired), though it's undoubtedly convoluted.

Regards

 

[excelos]

It's surprising how difficult it is to construct the equivalent COUNTIF(S) construction to:

=SUMPRODUCT(N(0+A1:A100>65))

The best I can come up with is:

=SUM(COUNTIFS(A:A,REPT("?",{0,1}+LEN(65))&{"","*"},A:A,">'"&{65,0}))

(replace the two instances of 65 as desired), though it's undoubtedly convoluted.

Regards

I think it would be much easier with the VAL formula!
Any idea?

 

[XOR LX]

Which formula? VAL's VBA, isn't it? I thought we were talking worksheet functions here?

There's a VALUE function, though, unlike with SUMPRODUCT, you cannot first apply functions to the ranges referenced as the criteria ranges within COUNTIF(S), SUMIF(S), etc. Hence my convoluted workaround.

Regards