Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Countif Formula not working, why?

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 ...

  1. #1
    New Member
    Join Date
    Nov 2009
    Posts
    19

    Question Countif Formula not working, why?

    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!!!

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Countif Formula not working, why?

    Hi and welcome to the Board!!
    Use SUMPRODUCT
    Code:
    =SUMPRODUCT(--(F4:F500>=4000)*--(F4:F500<5000))
    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

  3. #3
    New Member
    Join Date
    Nov 2009
    Posts
    19

    Default Re: Countif Formula not working, why?

    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?

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,950

    Default Re: Countif Formula not working, why?

    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
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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

  5. #5
    New Member
    Join Date
    Nov 2009
    Posts
    19

    Default Re: Countif Formula not working, why?

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

  6. #6
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Countif Formula not working, why?

    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

  7. #7
    New Member
    Join Date
    Nov 2009
    Posts
    19

    Default Re: Countif Formula not working, why?

    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?

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,950

    Default Re: Countif Formula not working, why?

    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?
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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

  9. #9
    New Member
    Join Date
    Nov 2009
    Posts
    19

    Default Re: Countif Formula not working, why?

    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?

  10. #10
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Countif Formula not working, why?

    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

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com