IFS and NOT BLANK

[lemery]

My formula is not working when fields are NOT BLANK.
I would like to have it say company append if Either column T or AM are NOT BLANK and A is 'highrisk' or 'mediumrisk' and I would like it to say unknown if both column T and AM are blank

 

[Misca]

It's been too long day for me to understand all the cell references (all the possible combinations of mixed rows + columns) in your current formula so I can't give you an exact answer, but looks like you could use the LEN(AM20&T20) in your formula to make it easier. If it's 0 then both cells are blank. Any other value means there's something in at least one of the cells. Is this enough or do you need to know which one is the empty one? And what should happen if there's a value in both cells?

Similarly maybe you could check if the word "risk" can be found in A20 or A7 using ISNUMBER(SEARCH("risk",A20&A7)). Replace the "risk" with more specific terms if needed. You have to check more than one word you can sum the values to get an OR or multiply them for AND (TRUE = 1 and FALSE = 0)

 

[lemery]

Thank you for your help! The Risk part isn't the part drawing the error. I have pulled the string apart by each section and they all work except for when I include "*" When the formula finds text in T or AM, it breaks and says N/A.

The end goal is to have the formula bring back Company Append when it has HighRisk or MediumRiskin Column A and text in either T or AM. Currently, it's coming back as N/A so I can do a Find/Replace, but if I can figure out how to fix it, to save a step, it would be great!

 

[JimM]

Does this work

=IF(AND(OR(T1<>"",AM1<>""),OR(A1="highrisk",A1="mediumrisk")),"Company Amend","unknown")

 

[JimM]

The above assumes that is there is something in T or AM then A can only be highrisk or mediumrisk though

 

[AlanY]

try this

=IF(AND(ISBLANK(T20),ISBLANK(AM20)),"unknown",IF(AND((LEN(T20)+LEN(AM20))>0,OR(A20="highrisk",A20="mediumrisk")),"Company Append","X"))

 

[lemery]

This is a segment of a larger IFS statement. Will these work in IFS statements?

 

[AlanY]

can't see why not

 

[mikerickson]

Equals does not accept wildcards. A1="*" is true only if A1 contains exactly an asterisk.

 

[lemery]

Does this work

=IF(AND(OR(T1<>"",AM1<>""),OR(A1="highrisk",A1="mediumrisk")),"Company Amend","unknown")

Does this work

=IF(AND(OR(T1<>"",AM1<>""),OR(A1="highrisk",A1="mediumrisk")),"Company Amend","unknown")

This part was very helpful: (T1<>"",AM1<>"") It worked for a different formula I was working on that only had one Valueif true. When I had to values (value if true and value if false:"Company Amend","unknown") it errs saying I have too few arguments. I think this is becaseu I'm using IFS instead of IF.