COUNTIF where criteria compares year only in the range

Hi,

I searched the forum looking for an answer to my problem. I could not find it. My apology in advance if this post is a duplicate.

Basically, I have a column F that has DATE formatted as mmmm-yy. In a different column K, I would like to add the number of times a particular year (yyyy) appears in column F. The particular year for comparison is the heading of column K and is formatted as yyyy.

I thought the function to use would be COUNTIF(range, criteria) . Below is the formula I came up with. However, when I evaluate it, it shows me the criteria as True but the answer is zero. I cannot figure out what am I doing wrong.

{=(COUNTIF('[Sheet Org.xlsx]Column F'!$F$65:$F$69,YEAR('[Sheet Org.xlsx]Column F'!$F$65:$F$69)=YEAR(K6)))}

The column F rows 65-69 has exactly two dates with the Year 2015. They are displayed as Jan-15 and Apr-15. The column K heading (K6) has the year 2015 (formatted as yyyy). The answer should be 2.

Any help would be appreciated.

Try

=COUNTIFS('[Sheet Org.xlsx]Column F'!$F$65:$F$69,">="&DATE(YEAR(K6),1,1),'[Sheet Org.xlsx]Column F'!$F$65:$F$69,"<"&DATE(YEAR(K6)+1,1,1))

Thank you both for your reply.

I tried the SUMPRODUCT. That worked. I believe it works with Excel 2003 as well.

COUNTIFS with the arguments as shown would work too. I was using COUNTIF thinking that it is only one criteria (with computation in it). Silly me!