Countif Formula not working, why?

[lansawc]

I have a column of numbers ranging from 0000-9999. They are not in order and multiple numbers are used more than once. I would like to know how many times the numbers in the range of 4000-4999 appear. I am trying to use the formula =COUNTIF(F4:F500,"=4????") or =COUNTIF(F4:F500,"=4*") but neither are working. If I do a single number like this =COUNTIF(F4:F500,"=41420"), it will count them for me. HELP!!!

 

[lenze]

Hi and welcome to the Board!!
Use SUMPRODUCT

=SUMPRODUCT(--(F4:F500>=4000)*--(F4:F500<5000))

lenze

 

[lansawc]

The result that I got from your formula is the same as mine. It shows a zero (0) in the cell I am trying to put the formula in. Anything else you can think of?

 

[Jonmo1]

It sounds like the numbers in F4:F500 are not really numbers, but "Numbers stored as Text"
This is a very common and annoying problem...

Try
=SUMPRODUCT(--(LEFT(F4:F500,1)="4"),--(LEN(F4:F500)=4))

Or even better, use Data - Text to Columns to convert the "numbers stored as text" to actual numbers

 

[lansawc]

tried the new formula it didn't work and I tried the text to columns and that didn't help. Anything else maybe?

 

[lenze]

Check you cells to see if they are numbers
=ISNUMBER(F4) should return TRUE
If not, Text to Columns should fix that or
Jonmo's formula should work

lenze

 

[lansawc]

I created a new column next to the F column. I entered =isnumber(F4) at the top and I dragged it down to the very last number I had in the F column and next to all my numbers it says true. Any ideas where I should look?

 

[Jonmo1]

I would now be checking if the values of the numbers are what you think they are.

Maybe there is some special format on the cells that makes it look like 4000, when it really isn't...

Try copying the f column, paste specail values onto another column.

format that column as General

does that reveal anything?

 

[lansawc]

I copied the column and pasted it into a brand new worksheet. I then made the column wider. All my numbers were on the right side of the column. I made did a format on them to general and nothing changed. I did a format to text and they all went to the left side of the column. I took the two formulas I got from Mr Excel and put them on the same worksheet and changed the columns from "F" to "D", the column I am using on the new worksheet, and the formulas gave the same results. I tried my countif formula and I got the same results as before. Then I tried just a specific number and the formula worked just like in my original post. Anything else maybe I could try?

 

[lenze]

What is the LEN of your cells
=LEN(F4)

lenze