# Solved: Excel 2003 - COUNTIF() Date Comparison

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

Not open for further replies.

Joined:
Mar 29, 2002
Messages:
158
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 !

Joined:
Apr 11, 2009
Messages:
5,467
First Name:
Josiah
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())

Joined:
Mar 29, 2002
Messages:
158
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 ! )

As Seen On