IF statement added is not working

toony

New member
Joined
Mar 6, 2014
Messages
49
Reaction score
0
Points
0
Location
UK
Excel Version(s)
Excel 2016
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
 

OTPM

New member
Joined
May 19, 2014
Messages
5
Reaction score
0
Points
0
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:
=IF(RIGHT(A3,5)="(Sub)","OK","NO") adjust as necessary.
Tony
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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?
 

toony

New member
Joined
Mar 6, 2014
Messages
49
Reaction score
0
Points
0
Location
UK
Excel Version(s)
Excel 2016
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))
 
Top