COUNTIF using cell reference and wildcard text

[medeirad]

Hi Everyone,

For our orders there is a large comments field, somewhere in there always the standard text of the date, the person's initials and "PROCESSED ORDER" to signify they processed the order (e.g. "08/14/2017 EAS - PROCESSED ORDER"). There is often un-needed text before and after this so one would need to use a wildcard search.

What's tricky is that I want to count the number of times there is a certain date (entered in cell C1 in my sheet) followed by the standard text "EAS - PROCESSED ORDER".

I tried the following to no avail: =COUNTIFS('Paste HD Report'!$AT:$AT,"*&C1&*&*EAS - PROCESSED ORDER*")

Does anyone have an idea to make this work?

 

[mrhstn]

If it's a date followed by the set string of text then you may want to use the TEXT function

=COUNTIF('Paste HD Report'!$AT:$AT, TEXT(C1, "mm/dd/yyyy")&"*EAS - PROCESSED ORDER*")

 

[medeirad]

Thanks so much for the reply, mrhstn. Unfortunately I'm having some difficulty getting that to work. To verify I opened a new workbook, entered today's date in cell C1 and typed "08/15/2017 EAS - PROCESSED ORDER" in cell C2 then tried the formula below (=COUNTIF(C2, TEXT(C1, "mm/dd/yyyy")&"*EAS - PROCESSED ORDER*"). That count returned a 0 so I'm back to the drawing board. Is there anything else that could possibly work?

 

[Jonmo1]

It works for me..

Did today's date get entered as a real date in C1 ?

What does this return
=ISNUMBER(C1)

 

[medeirad]

Oops, had a typo in the formula and now it works for me in the example application. However, I ultimately want it to count if the date + text is present in field that has other text before and after it.

For example if in C2 above I typed: "blah blah blah 08/15/2017 EAS - PROCESSED ORDER blah blah blah" and wanted to use our formula (=COUNTIF(C2, TEXT(C1, "mm/dd/yyyy")&"*EAS - PROCESSED ORDER*")) it returns 0 because presumably the wild card only references the processed order text? How can I get it to count if this is in the middle of other text?

 

[Jonmo1]

In that case, you also need an * before the date in countif

=COUNTIF(C2, "*"&TEXT(C1, "mm/dd/yyyy")&"*EAS - PROCESSED ORDER*")