How to convert text into numbers to use with COUNTIFS?

excelos

Well-known Member
Joined
Sep 25, 2011
Messages
591
Office Version
  1. 365
Platform
  1. Windows
Hello

AV column contains numbers stored as text.
How can I use COUNTIF with that data?
I tried the below, but it does not work.
[=COUNTIF(VALUE(UDE!$AV:$AV),">69")]

Thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Can you convert column AV to numbers, so they are easier to work with?
It is pretty easy to do this in one fellow swoop using Text to Columns on that column.
 
Upvote 0
What would the VBA for that please?
You can get this code yourself really easily by using the Macro Recorder.
Just turn on the Macro Recorder, select column AV, go to Text to Columns from the Data menu, and click Finish.
Then stop the Macro Recorder and view the code that you just recorded.

The Macro Recorder is a great tool to get snippets of code like this, without ever having to write any VBA code yourself.
 
Upvote 0
Thanks but what does Text to Columns has to do with it?
 
Upvote 0
Text to Columns sort of "re-imports" the data. So values that look like numbers will come in as numbers.
So it will quickly converts numbers entered as text to numbers (assuming we are talking about hard-coded values and not formulas here).
Try it an see for yourself.
 
Upvote 0
You are welcome.

You can also use Text to Columns when you want to split one column into many columns, based on width or delimiters.
It works much like the Import Wizard does.
 
Upvote 0
Hi,

If you really want a formula, you can try this:

=SUMPRODUCT(--(AV:AV+0>69))

to Count all numbers (stored as Text) in Column AV that is Greater than 69
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top