1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 - COUNTIF() Date Comparison

Discussion in 'Business Applications' started by shaygate, Jan 26, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. shaygate

    shaygate Thread Starter

    Joined:
    Mar 29, 2002
    Messages:
    157
    Basicly all I want to do is get a count of entries in a list, if the date in the cell is less than today. The formula I have been trying to use is =COUNTIF(O73,(O73<TODAY())) but its not working so I am obviously doing something wrong can anybody help ?

    I have a workround by using a hidden column and a few other arguments etc. but its cumbersome I like the elegance of a simple formula and it should be easy ! :D
     
  2. Ent

    Ent Josiah Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,461
    The syntax for countif is =Countif(range,test)

    Range should be a range of cells, your formula will at most return 1 if O73 < today.
    If you want to count all the ones between O1 and O100 you'd put O1:O100 in the place of range.

    Test works on the cell being inspected automatically so you don't need to repeat the cell. What you do need is quote marks (not parentheses) around the criteria. For example to count the numbers in O1 to O100 whose value is less than 4 you'd write =countif(O1:O100,"<4")

    Because Today() is itself a function it doesn't go within the quotes. It is joined to the less than sign by an ampersand. So in your particular case you'd want

    =COUNTIF(O1:O100,"<"&TODAY())
     
  3. shaygate

    shaygate Thread Starter

    Joined:
    Mar 29, 2002
    Messages:
    157
    Thats perfect, works like a treat ..... Thanks

    (By the way I was counting in a range like you suggested in your answer but the example I posted was based on only one cell as I was playing with it trying to get it to work ! :D )
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/977055