COUNTIF with dates

J

Jock

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?
 
J

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))
 
M

Max

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)
 
J

Jock

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
 
L

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
Click to expand...
 
Top