COUNTIF with dates

[Guest]

Dates in a column are formatted thus: 18/06/2007.
How can I adapt the COUNTIF formula to look at these dates (going back to
1986) and report back on the year part?
Any ideas?

 

[JE McGimpsey]

What do you expect COUNTIF() to report?

If you want to count the number of dates in 1998, one way:

=COUNTIF(A:A,"<"&DATE(1999,1,1))-COUNTIF(A:A,"<"&DATE(1998,1,1))

or

=SUMPRODUCT(--(YEAR(A1:A10000)=1998))

 

[Guest]

One guess ..

Something like this in say, B1:
=SUMPRODUCT(--(YEAR(A1:A100)=1987))
will count the number of dates within A1:A100 with the year 1987
(dates within A1:A100 are assumed real dates)

 

[Guest]

I would like to know how many times '1998' appears in the list (and so on for
the other years too), but the problem is where there is text in a cell as
well as a date that the formula cames back with #value

 

[Lori]

If there's text too maybe try array-entered (using ctrl+shift+enter)
eg:

=COUNT(FIND(1998,A$1:A$100))

I would like to know how many times '1998' appears in the list (and so on for
the other years too), but the problem is where there is text in a cell as
well as a date that the formula cames back with #value