COUNTIF Using Cell Reference

[djmyers]

Hello Excel Professionals:

I am trying to count each instance of a particular date within a text string. When I hard-wire the date into the formula, i.e...

=COUNTIF(I:I,"*7/27/2017*")
​

...I get the count I'm looking for.

My preference would be to use a cell reference for the date, i.e...

=COUNTIF(I:I,"*"&K2&"*")
​

...however I am unable to make that particular formula work.

Any suggestions?

Thanks!

 

[Beyond_avarice]

Make sure your "K2" value is formatted as "Text". Otherwise your search criteria will search for 42943 and not "7/27/2017".

 

[MrTeeny]

try forcing it to a text string like this

=COUNTIF(I:I,"*"&TEXT(K2,"dd/mm/yyyy")&"*")

 

[djmyers]

Thanks, Beyond_avarice. Formatting both the string column (I:I) and K2 as text seemed to do the trick.

 

[djmyers]

Thank you for your reply, MrTeeny. Your suggestion of forcing K2 to text isn't working for me. Not sure why.