occurance in each year

[Guest]

I have a 850 dates in a column with various years. I want to count the number
of occurances in each year. I use the following formula but continue to
reveiw 0. The formula I am using is;
=COUNTIF(k2:k850,"(date(2007/1/1)>=(date(2007/12/31)")

What I am lookin for is the number of entries in 2007.
Can anyone tell me what I am doin wrong/
Thanks
Dick

 

[Guest]

Try

=SUMPRODUCT(--(YEAR(K2:K850)=2007))

 

[Guest]

=SUMPRODUCT(--(YEAR(K2:K850)=2007))

 

[David Biddulph]

=COUNTIF(K2:K850,">="&DATE(2007,1,1))-COUNTIF(K2:K850,">"&DATE(2007,12,31))
or
=COUNTIF(K2:K850,">="&--"1/1/2007")-COUNTIF(K2:K850,">"&--"31/12/2007")

 

[Guest]

=SUMPRODUCT(--(YEAR(K2:K850)=2007))
I entered the above formula, and receive a value error. I went back and made
sure the dates wer formatted as date, and received hte same error. The dates
are entered as 01/05/2007

did I not copy the formula correctly

All the best,
Dick

 

[Guest]

If I change one of my sample dates to a non-date text entry up pops a #VALUE
error. This, and the way you showed the dates in the criteria for your
original post suggests that you have text entries that look like dates to you
but that Excel can't translate.

I'd suggest you test the formula on a small subset of data that you now to
be date values (if you reformat the cell to General and the value in the cell
doesn't change appearance, it's not a date)

 

[Mike G]

I would suggest that you create a test sheet and copy 3 or so dates in there
(k column)i.e. 1/1/07, 2/3/06,4/5/07 and then cut/past the formula in
another cell. I just did that and got the correct answer = 2. If
successful, then go back to your real sheet and figure what the difference
is.

 

[Guest]

Mike,
I created a test sheet and it worked. I went backto the original and
formtted the colume to a date. repasted the formula and still receiveda value.

I copied the data to new sheet, witht he same results. I took the first
cell k2 and tried to reformat, recived the same resluts.

can you by any cance point me to how to fis the problem. I doid not create
the work book.

All the best
Dcik

 

[Fred Smith]

Just reformatting the original data will not work. Reformatting will not change
text entries to numbers.

Try the following:

Enter 0 in an unused cell.
Copy it.
Highlight your column of dates.
Use Edit>Paste Special...>Add

That should change your dates from text to valid Excel dates, and allow
Sumproduct to work.