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

Jock

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

In article ,
Jock wrote:

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?

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jock" wrote:
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?
--
tia

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
--
tia

Jock


"JE McGimpsey" wrote:

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

In article ,
Jock wrote:

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?

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

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



On 18 Jun, 17:35, Jock wrote:
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
--
tia

Jock

"JE McGimpsey" wrote:
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))

In article ,
Jock wrote:

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?