Countif the text in a cell, not text in a formula - Help!

Hey all.

Having a little difficulty with this one.

Essentially, I am trying to create a formula that counts the number of emails in a list that have a certain subject title between two dates, but crucially DOES NOT count them if there are certain words/strings in the subject like 'automatic reply'.

My formula works thus far. It counts the subject title occurrences with variations from the cell (G7 in pic) and then it doesn't count the other words that are in the rest of the formula.

I want to tidy the formula up so that so that I can simply drag it down, like the formula for cell value in G7. When I try to add <> in the same format, to not count the 'negative replies' from B7 down it doesn't let me do it. Can anyone let me know what might work here please?

Like I said I want the whole formula drag-able down. There is a file attached.

=COUNTIFS('Email Replies (add to end)'!$C$2:$C,""&Text($G7,"0")&"*",'Email Replies (add to end)'!$C$2:$C,"<>*Automatic reply*",'Email Replies (add to end)'!$C$2:$C,"<>*Request received*", 'Email Replies (add to end)'!$C$2:$C,"<>*Ticket*", 'Email Replies (add to end)'!$C$2:$C,"<>*automatische Antwort*", 'Email Replies (add to end)'!$C$2:$C,"<>*Spam*", 'Email Replies (add to end)'!$C$2:$C,"<>*Commercial*",'Email Replies (add to end)'!$D$2:$D,">"&I7,'Email Replies (add to end)'!$D$2:$D,"<="&K7)

ThanksScreenshot 2022-07-20 at 11.52.54.png

Noted Jan, thanks for the help.

The dates are in the email replies tab in column D. The dates it needs to count between are in the overview tab column I & K.

The formula is needed in the overview tab, in column J. Let's use J7 as the example though.

B6 to B16 in overview tab shouldn't be included in the count.

thanks

You are a saviour!

Case insensitive is perfect.

Super hero. Thanks for your help here. This is solved.