+ Reply to Thread
Results 1 to 12 of 12

countif does not recognize < and > symbols in excel cell values

  1. #1
    Registered User
    Join Date
    12-14-2012
    Location
    buena park, CA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Angry countif does not recognize < and > symbols in excel cell values

    Hi all, I am new at this so please bear with me. I'm trying to count cells that match certain criterias, everything works as expected when the cells are just numbers, but in some cells there are < and > signs before the numbers (i.e. >75) and I could not get it to work with countif. Anyone has any suggestions? Thanks alot!!

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: countif does not recognize < and > symbols in excel cell values

    The syntax for > and < in Countif/sumif is

    =COUNTIF(A:A,">"&B1)

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: countif does not recognize < and > symbols in excel cell values

    Or are you saying that the > symbol is actually held in the reference cell..?

    =COUNTIF(A:A,B1)
    And B1 is >75

    That's the same as writing
    =COUNITF(A:A,">75")

    That syntax works for me as well.

  4. #4
    Registered User
    Join Date
    12-14-2012
    Location
    buena park, CA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Angry Re: countif does not recognize < and > symbols in excel cell values

    Thanks for your quick reply! However, the reference cell does in fact contain the > symbol, it is also the cell to be counted if this all works out. I tried both ways, with named range and ">75" in quotations but it still counts 0 for some reason... Any ideas? Thx again!

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: countif does not recognize < and > symbols in excel cell values

    If the >75 is being held in a cell, then it should NOT be in quotes.

    =COUNTIF(A:A,B1)
    And B1 is >75 wihtout any quote marks in B1

  6. #6
    Registered User
    Join Date
    12-14-2012
    Location
    buena park, CA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countif does not recognize < and > symbols in excel cell values

    Yes you are right, the > symbol is in the reference cell and I tried both ways above but both did not count...

  7. #7
    Registered User
    Join Date
    12-14-2012
    Location
    buena park, CA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countif does not recognize < and > symbols in excel cell values

    I did =COUNTIF(coli, >75) but excel gave me an error...

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: countif does not recognize < and > symbols in excel cell values

    Wait a second...

    Take this sample countif
    =COUNTIF(A1:A100,B1)
    Which number(s) have the < or > symbols before them..the ones in A1:A100, or the one in B1 ?


    It's probably time for you to post a sample book..

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: countif does not recognize < and > symbols in excel cell values

    Quote Originally Posted by chengg02 View Post
    I did =COUNTIF(coli, >75) but excel gave me an error...
    That needs to be written like
    =COUNTIF(coli, ">75")

  10. #10
    Registered User
    Join Date
    12-14-2012
    Location
    buena park, CA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countif does not recognize < and > symbols in excel cell values

    OK, I think I seemed to have found a solution... =COUNTIF(coli,"*75")
    and it counted... would this be correct?

  11. #11
    Registered User
    Join Date
    12-14-2012
    Location
    buena park, CA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countif does not recognize < and > symbols in excel cell values

    Thanks for your responses!!

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: countif does not recognize < and > symbols in excel cell values

    Quote Originally Posted by chengg02 View Post
    OK, I think I seemed to have found a solution... =COUNTIF(coli,"*75")
    and it counted... would this be correct?
    I don't think that will count the cells that DON'T have the < symbol..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Greater Than or Less than symbols to ectract cell values
    By GatorsBucs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2013, 10:34 AM
  2. Excel won't recognize cell value as a number
    By Fabienne88 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2013, 10:40 AM
  3. [SOLVED] enable Excel to recognize Cell Colors
    By Scarface in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-18-2005, 03:06 PM
  4. [SOLVED] Recognize more stock ticker symbols?
    By Bob in forum Excel General
    Replies: 2
    Last Post: 01-26-2005, 03:06 AM
  5. Smart Tag in Excel does not recognize some stock symbols
    By Chambersteacher in forum Excel General
    Replies: 0
    Last Post: 01-20-2005, 08:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1