# Countif to return # of cells with dates containing X year

#### mbeardsl

##### New Member
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))

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

##### MrExcel MVP
I1: 2016

J1:

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

#### Eric W

##### MrExcel MVP
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

##### New Member
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) ***

##### MrExcel MVP
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

##### MrExcel MVP
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

##### New Member
Sorry, can you explain how this one works?

#### Tetra201

##### MrExcel MVP
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.

Last edited:

#### mbeardsl

##### New Member
Thank you, very slick!

You are welcome.

Replies
2
Views
310
Replies
8
Views
451
Replies
2
Views
304
Replies
3
Views
243
Replies
1
Views
283

1,195,905
Messages
6,012,218
Members
441,682
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back