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

[Forlanman]

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

 

[JamesCanale]

MrExcelPlayground11.xlsx
	A	B	C
1	apple	John is silly	3
2	pear	Jamie ate an apple
3	tree	He is one foot tall
4	foot	Sally is smart
5		What is thy bidding my master?
6		I fell out of a tree
Sheet13

Cell Formulas
Range	Formula
C1	C1	=SUM(--(MMULT(SEQUENCE(1,COUNTA(A1:A4),1,0),TRANSPOSE(IFERROR(SEARCH(TRANSPOSE(A1:A4),B1:B6),0)))=0))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Criteria in Countifs to exclude cells that contain any words in a list
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.