countif for current year when date is in format mm/dd/yyyy

[adunlap13]

Hi,

I am trying to count the number of times the current year is in a single column for certain people. The below is what I have so far but I keep getting 0 returned.

=COUNTIFS($H25:$H500,"=WP",$J25:$J500,"="&YEAR(NOW()))

Thank you in advance for the help!

 

[seguin85]

Try changing "="&YEAR(NOW())) to:

">="&DATE(YEAR(NOW()),1,1

this says that countif the date is larger than jan1, 2016

 

[Aladin Akyurek]

Try...

=COUNTIFS($H25:$H500,"=WP",$J25:$J500,">="&DATE(YEAR(TODAY()),1,1),$J25:$J500,"<="&TODAY())

 

[wideboydixon]

That will work if there are no dates in 2017. If there's a possibility that you will have dates in future years then:

=SUMPRODUCT(($H25:$H500="WP")*(YEAR($J25:$J500)=YEAR(NOW())))

You can change YEAR(NOW()) to a hard-coded year if necessary

=SUMPRODUCT(($H25:$H500="WP")*(YEAR($J25:$J500)=2016))

WBD

Note: Cross-posted with Aladin

 

[adunlap13]

Try...

=COUNTIFS($H25:$H500,"=WP",$J25:$J500,">="&DATE(YEAR(TODAY()),1,1),$J25:$J500,"<="&TODAY())

That worked perfectly, thanks a lot!

I did have to add $J25:$J500 before the final "<="&TODAY(). Just wanted to put that in there if any other people glanced at this.

 

[Aladin Akyurek]

You are welcome.