countif and date formats

[shnim1]

Hi

I have a list of dates (in 12/12/2004 format) in a column. I need t
seach how many dates are in the list with a year of 2004, regardless o
day or month. I was using the countif function (=countif(a2:z2,"=2004"
but it does not recognize any cells since there are months and day
attached in the list. I also cannot change the list to show just year
either....

I hope that this makes sense, and thanks for any hel

 

[Dave Peterson]

Column or Row?

=sumproduct(--(YEAR(A2:Z2)=2004))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html