Countif to return # of cells with dates containing X year

[mbeardsl]

I'm struggling with getting a formula to work.

I have dates in column H. I simply want to count how many times a date occurs with 2016 (or a referenced cell) in the year.

This is as close as I've gotten, but isn't what I'm wanting as the dates will eventually go past 2016 and then the count will be including 2017 dates.

=COUNTIF(H2:H15,">"&DATE(2016,1,1))

Thanks in advance!

 

[Aladin Akyurek]

I1: 2016

J1:

=COUNTIFS(H:H,">="&DATE(I1,1,1),H:H,"<="&DATE(I1+1,1,1)-1)<strike></strike>

 

[Eric W]

Try:

=COUNTIFS(H2:H15,">="&DATE(2016,1,1),H2:H15,"<="&DATE(2016,12,31))

and you can put 2016 in a cell if you want.

 

[mbeardsl]

Thanks guys. Those work perfectly.

Instead of the first one, I could just use this instead for slight simplicity?

=COUNTIFS(H:H,">="&DATE(I1,1,1),H:H,"<"&DATE(I1+1,1,1)) ***vs "<="&DATE(I1+1,1,1)-1) ***

 

[Aladin Akyurek]

Thanks guys. Those work perfectly.

Instead of the first one, I could just use this instead for slight simplicity?

=COUNTIFS(H:H,">="&DATE(I1,1,1),H:H,"<"&DATE(I1+1,1,1)) ***vs "<="&DATE(I1+1,1,1)-1) ***

Yes, you can...

 

[Tetra201]

I1: 2016

J1:

=COUNTIFS(H:H,">="&DATE(I1,1,1),H:H,"<="&DATE(I1+1,1,1)-1)<strike></strike>

There is a potential pitfall in this formula: if column H has dates with times, anything beyond 31-JAN-2016 00:00:00 will not be counted.

Here is a shorter and a more robust formula:

=COUNTIFS(H:H,">="&I1&"-1-1",H:H,"<"&I1+1&"-1-1")

 

[mbeardsl]

Sorry, can you explain how this one works?

 

[Tetra201]

Here it is:

Let's assume cell I1 has a value of 2016. Then I1&"-1-1" yields "2016-1-1", and I1+1&"-1-1" yields "2017-1-1".

The formula thus becomes =COUNTIFS(H:H,">=2016-1-1",H:H,"<2017-1-1"), and Excel does the job.

 

[mbeardsl]

Thank you, very slick!

 

[Tetra201]

You are welcome.