CODEWITHSUNDEEP
google-sheetssubstringcountif
Clay Shirky
Clay Shirky

Reputation: 133

COUNTIF numerical substring in Google Sheets?

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.

Upvotes: 1

Views: 273

Answers (2)

CMB
CMB

Reputation: 5163

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:

enter image description here

Reference:

YEAR()

Upvotes: 1

Aresvik
Aresvik

Reputation: 4630

Try:

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

Upvotes: 1

Related Questions