Reccomendation for Countif when data contains "2018"

[white84]

I am trying to create lookup table that will return certain criteria based on annual date ranges. So I have a data validation / drop down list to change from 2018, 2017, 2016. But I'm drawing a blank how to create a CountIf based on the year within the text of the cell range.

I have done this in the past

[=IF(TODAY()>$D$4,COUNTIFS($E:$E,">=3/1/2018",$E:$E,"<=3/31/2018")/CODE]

But that won't work in this scenario. 

Thanks in advance!

 

[Special-K99]

Does this work?

A1 contains the year

=IF(TODAY()>$D$4,COUNTIFS($E:$E,">="&DATEVALUE("3/1/"&A1),$E:$E,"<="&DATEVALUE("3/31/"&A1)))

 

[white84]

I think we're on to something here. The code I provided before probably confused things more than helped.

I have a list of dates i.e. 5/3/2017
10/10/2016
4/30/2018
11/2/2016
11/30/2017
3/13/2017
11/9/2017
3/19/2018

But I want to be able to run a few codes based on the year from the drop down. One code example

=COUNTIFS('Tracker New Model'!$A:$A,"Active",'Tracker New Model'!$E:$E,C$1)

 

[Aladin Akyurek]

What do you have exactly in C1 - a year like 2017?

 

[white84]

What do you have exactly in C1 - a year like 2017?

For this C1 contains a program/project name. I have not entered any date criteria yet. I plan to reference another cell, maybe A9, with the dropdown for 2016, 2017 or 2018. And I would like to scan a column, $G:$G, for the year within the dates.

 

[Aladin Akyurek]

For this C1 contains a program/project name. I have not entered any date criteria yet. I plan to reference another cell, maybe A9, with the dropdown for 2016, 2017 or 2018. And I would like to scan a column, $G:$G, for the year within the dates.

Something like...

=COUNTIFS('Tracker New Model'!$A:$A,"Active",'Tracker New Model'!$E:$E,C$1,'Tracker New Model'!$G:$G,">="&DATE(A9,1,1),'Tracker New Model'!$G:$G,"<"&DATE(A9+1,1,1))

 

[white84]

Something like...

=COUNTIFS('Tracker New Model'!$A:$A,"Active",'Tracker New Model'!$E:$E,C$1,'Tracker New Model'!$G:$G,">="&DATE(A9,1,1),'Tracker New Model'!$G:$G,"<"&DATE(A9+1,1,1))

Exactly what I needed! Thank you!

 

[Aladin Akyurek]

Exactly what I needed! Thank you!

You are welcome.