Hi and welcome to the Board!!
Use SUMPRODUCTlenzeCode:=SUMPRODUCT(--(F4:F500>=4000)*--(F4:F500<5000))
This is a discussion on Countif Formula not working, why? within the Excel Questions forums, part of the Question Forums category; I have a column of numbers ranging from 0000-9999. They are not in order and multiple numbers are used more ...
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!!!
Hi and welcome to the Board!!
Use SUMPRODUCTlenzeCode:=SUMPRODUCT(--(F4:F500>=4000)*--(F4:F500<5000))
If you have to tell your boss you're good with Excel, you're NOT!!
All I know about Excel I owe to my ignorance!
Scotch: Because you don't solve great Excel problems over white wine
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?
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
Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
tried the new formula it didn't work and I tried the text to columns and that didn't help. Anything else maybe?
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
If you have to tell your boss you're good with Excel, you're NOT!!
All I know about Excel I owe to my ignorance!
Scotch: Because you don't solve great Excel problems over white wine
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?
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?
Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
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?
What is the LEN of your cells
=LEN(F4)
lenze
If you have to tell your boss you're good with Excel, you're NOT!!
All I know about Excel I owe to my ignorance!
Scotch: Because you don't solve great Excel problems over white wine
Like this thread? Share it with others