COUNTIF numerical substring in Google Sheets?

Question

I have a column of dates in somewhat inconsistent formats, but always ending with the four digit year, like:

    May 4, 2021
    Apr. 23, 2021
    Mar 7, 2021

if I delete the month and day from every cell, I can count occurrences of years with e.g.

    countif(B:B, 2021)

However, I'd rather not delete the month and date, but I can't figure how to get COUNTIF to match on the year as a substring. Neither

    countif(B:B, "*2021")

or

    countif(B:B, "*"+2021)

work, and I can't figure out why. Any advice greatly appreciated.

user_15784415 · Answer

Try:

=arrayformula(countif(right(B:B,4),2021))

user_14606049 · Answer

Solution:

If these are all in date format (as default when these are entered), you can also use YEAR() to extract the year, then count:

=arrayformula(countif(year(B:B),2021))

But if some entries are text, then use this formula instead to convert everything to text first.

=arrayformula(countif(right(text(B:B,"mmm dd, yyyy"),4),2021))

Output:

Reference:

YEAR()