COUNTIF partial date, using year

[Enzo_Matrix]

I've gone through a few of the pages that are asking similar countif questions regarding dates and I haven't found one that works for my circumstances yet. Any help would be appreciated.

Column B has my date ranges in format January 1, 2017 (this can be changed). I have data spanning 4 years and I want to count how many instances there are for the year 2015, 2016, 2017, 2018 without adding another column with just the year in it.

I have tried this and it has not worked

=COUNTIF(B:B,"*2017*")

please help

 

[Special-K99]

"Column B has date ranges"

Are these dates text or proper dates?

If they are proper dates then

=SUMPRODUCT(--(YEAR(B1:B1000)=2017))

If they are text then

=SUMPRODUCT(--(RIGHT(B1:B1000,4)+0=2017))

Copy and amend the formula for other years

 

[wideboydixon]

If these are actual dates (and not text) then:

=COUNTIFS(B:B,">="&DATE(2017,1,1),B:B,"<"&DATE(2018,1,1))

WBD

 

[Enzo_Matrix]

This! this worked perfectly.

Why did the sumproduct work but the countif did not?

 

[Special-K99]

Excel dates are stored internally as numbers and do not contain 2017. They are a count of days since 1900 (I think).
So if you search for 2017 it won't be found.
"*2017*" searches for text not numbers.

You can put YEAR() around a range in a SUMPRODUCT but you cant do this with a COUNTIF.