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!!!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Countif Formula not working, why?
- Thread starter lansawc
- Start date
Excel Facts
Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
lansawc,
Can we have a screenshot of your data?
Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
Can we have a screenshot of your data?
Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
Upvote
0
=COUNTIFS(F4:F500,">=4000",F4:F500,"<=4999") in Excel 2007
or achieve the same result in earlier Excels by subtracting count of <4000 from count of <=4999. Or....there are dozens of ways to solve this
Lenze has a typo in his first equation. It should be:
=SUMPRODUCT(--(F4:F500>=4000),--(F4:F500<5000))</pre>
However, alarmingly, I note that SUMPRODUCT, and array formula generally don't update in Excel 2007 when I am using random numbers and press F9! That is, it works when I am in the cell formula and press enter (get same numbers as above) but doesn't update when I press F9, which generates new random numbers! Whoa! Have to check that out.
or achieve the same result in earlier Excels by subtracting count of <4000 from count of <=4999. Or....there are dozens of ways to solve this
Lenze has a typo in his first equation. It should be:
=SUMPRODUCT(--(F4:F500>=4000),--(F4:F500<5000))</pre>
However, alarmingly, I note that SUMPRODUCT, and array formula generally don't update in Excel 2007 when I am using random numbers and press F9! That is, it works when I am in the cell formula and press enter (get same numbers as above) but doesn't update when I press F9, which generates new random numbers! Whoa! Have to check that out.
Upvote
0
Jonmo1
MrExcel MVP
- Joined
- Oct 12, 2006
- Messages
- 44,061
If LEN is 5, then it is impossible for the number to be between 4000 and 4999....That's why you're getting a count of 0 for values between 4000 and 4999
If Len is 5, the smallest the number in the column can be is 10000 (count the zeros)...
perhaps you want
=SUMPRODUCT(--(F4:F500>=40000)*--(F4:F500<50000))
or maybe your numbers have decimal points that you don't see, like it's 400.0 or 4.000 or 40.00 or something like that...
Last edited:
Upvote
0
Upvote
0
So I posted the reply of "still looking for help" before I had not read the post from Jonmo at 703 ( i didn't see it). Once I seen that post it made me take another look at what was going on. I am but a rookie at formulas inside excel and when I took another look I realized what I had did. You are right about the 41420 not being lower than the 9999. I tried to post this right away but was not able to because my VPN dropped right after I posted the "still looking for help." With that being said. The very first reply would have worked if I said the number was between 00000-99999 and not 0000-9999. Thanks you all very much for all your help and I greatly appreciate it. I am so so sorry for the confusion I caused and I only wish I would have caught it sooner. Thanks again and you people are great!!!!
Upvote
0
Similar threads
