Criteria in Countifs to exclude cells that contain any words in a list

Hi everyone,

I've hit a bit of a brick wall with this one, so would be grateful for any input.

As the title says, I'm looking to create a criteria in a COUNTIFS formula that looks at a list of words in a column within one separate sheet and then in the total returned does not count any cells in a column in a third sheet that include any of these words/values.

This would need to be set up as a partial match so for example "apple" in the exclude sheet would stop the formula from counting a cell with "an apple tree".

I thought Index could maybe be used to accomplish this. Maybe INDEX and MATCH but I'm a bit lost on where I'm going with this.

INDEX("*"&INDIRECT("'"&E$16&"'!"&"$A:$A")&"*",0),"<>"&'Exclusion List'!C1:C2)

I have seen some suggestions for other methods outside of the count if to achieve this but as I have other criteria, I'm hoping there is a way to do this within the COUNTIFS function. I've included an image showing the full formula to give some context.

Any suggestions would be very welcome!

Thanks in advance.. David

Cross posted here: https://www.mrexcel.com/board/thread...-list.1213881/