How do you count cells with a certain partial string?

Question

I have cells with dates that are formatted in different ways (and too messy to re-format, also lots of data) but if I want to count how many dates are in October, for example, I was hoping to do a COUNTIF() or COUNTA(FILTER()) but I'm not sure how to search the cell for the partial string 10/ (this is the only unique combination of characters that I can think of that would ID only the correct month).

Solution

You can use * wild character, the wild character before the value means anything ending with and at the end means anything starting with...

=COUNTIF(A1:A10,"*10/*") - Has the text 10/ anywhere in the cell

=COUNTIF(A1:A10,"*"&B1&"*") - Cell B1 has the finding text

=COUNTIF(A1:A10,"10/*") - Has the text 10/ at start

=COUNTIF(A1:A10,"*10/") - Has the text 10/ at end

Comments

[JD Smith]

Brilliant answer, and avoids using functions to search the text unnecessarily.

[Bruno Nascimento]

Be careful. If you're using wildcards at the beginning, and the text to be searched only appears at the beginning, it won't be matched. In other words, if you use =COUNTIF(A1:A10,"*10/") and the text to be searched is 10/SOMETHINGELSE, it won't be matched.