count rows with date

[rjplante]

I have two columns, ColA with a date in it and the ColB with just the years from 2006 - 2017. I would like a formula which counts the numbers of rows in ColA that have years that match the year listed in ColB. I am currently using the formula listed below and it is not working. Please help. I know this is simple, just having a brain derailment on this one.

=COUNTIF(A:A,YEAR(B2))

Thanks in advance,

Robert

 

[Joe4]

If B2 is a number (like 2016) and not a date, here is one way:

=COUNTIFS(A:A,">=" & DATE(B2,1,1),A:A,"<=" & DATE(B2,12,31))

 

[rjplante]

all the data in ColB is in the date format.

 

[Joe4]

If the value in B2 is really a date and not a number, just change the reference to B2 in the formula I proposed to YEAR(B2)

=COUNTIFS(A:A,">=" & DATE(YEAR(B2),1,1),A:A,"<=" & DATE(YEAR(B2),12,31))

 

[rjplante]

when I try that formula, the result I get is 0. What am I doing wrong?

 

[Joe4]

Tell me what these two formulas return (assuming A2 is the first cell in column A with a date in it):

=ISNUMBER(A2)
=ISNUMBER(B2)

 

[rjplante]

I get TRUE for both when I checked using that formula.

 

[Caribeiro77]

Does this work?

Book1
	A	B	C
1	01-01-2007		2
2	01-01-2008
3	01-01-2009	2008
4	01-01-2010
5	01-01-2008
Sheet1

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(YEAR(A1:A10)=B3))