How to convert text into numbers to use with COUNTIFS?

excelos

Active Member
Joined
Sep 25, 2011
Messages
329
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
329

ADVERTISEMENT

Thanks but what does Text to Columns has to do with it?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
329

ADVERTISEMENT

Great trick! thanks!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top