1. ## IF statement added is not working

I’ve added condition IF(AL2218="Waste","YES","NO")), nothing happens when the condition is TRUE. I could put it at the beginning instead, but I need it to be YES/NO with the countif.

E2218 cell contains ‘Mark (Sub)’. Can I also ask with which statements the wild cards can be used? Will be probably using it this cell containing ‘Mark (Sub)’ where (Sub) never changes, but the name is changeable.

IF(ISNUMBER(SEARCH("sub",E2218)),IF(COUNTIF(\$AJ\$2040:\$AJ2218,AJ2218)=1,"YES","NO"),IF(AL2218="Waste","YES","NO"))

Many thanks

2. Hi
Make sure that the data in cell AL2218 contains no spaces, either before or after the text. If there are then what is seemingly a TRUE result to you will not be.
As for your second point you can use wildcards in virtually any search criteria. Can you advise exactly what you are looking for? You could try this formula:
Tony

3. You can simplify that to

=IF(ISNUMBER(SEARCH("sub",E2218)),IF(OR(COUNTIF(\$AJ\$2040:\$AJ2218,AJ2218)=1,AL2218="Waste"),"YES","NO"),"")

Are you sure sub is in E2218?

4. Thank you for your help. The shortening of the formula, you are very right. Didn't realised it. in regards to the wild cards, could I use the wildcard in the below formula? t doesn't seam to work for me:
=if(isnumber(search("*sub*",2218))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•