Marie has attended:
Excel Intermediate course
Excel Advanced course
Formulas with AND/IF with text
Hello, I attended the Advanced Excel Training course at Goldsmiths College, yesterday. I am trying to develop a spreadsheet now for the handling of secure letters. I have created columns directing users across a checklist of actions (or workflow)to take when processing these letters. they should enter a "X" or "yes" when they have performed a task... along this workflow. My question, is, how to I write a formula in a penultimate cell to read "completed" if the previous cells along the row each have the "yes" (or "X") mark? I have tried applying my IF /And training to no avail. I have also come across Cancatenate formula but these only return yes,yes,yes,yes in the results cell when I only wanted "yes". Any ideas please?
Many thanks
RE: formulas with AND/IF with text
Hello Marie,
Thank you for your question. Below is a formula that works with some data I tested:
=IF(AND(OR(A1="yes",A1="x"),OR(B1="yes",B1="x"),OR(C1="yes",C1="x")),"completed","")
This formula assumes that columns A, B, and C all need to contain either yes or x for a result saying "Completed". If any of these columns don't contain yes or x, the result will not be "Completed".
If you have more than 3 columns, simply include more OR arguments in the formula.
I hope this helps!
Kind regards
Marius Barnard
STL
RE: formulas with AND/IF with text
Thank Marius. You genius! This worked: =IF(AND(J6="X",K6="X",L6="X", M6="X"), "Completed")
Only now I'm being greedly and want to add an IF condition to the formula so that there is a false element, where any text is found in a certain column that reads "standard letter", the penultimate cell wont complete. Any ideas, please?
RE: formulas with AND/IF with text
My attempt fails:
=IF(AND(J6="X",K6="X",L6="X", M6="X") AND(G:I<>= "standard letter"), "Completed")
RE: formulas with AND/IF with text
or would the add on bit read:
=IF(B:B<>"standard letter","completed")
RE: formulas with AND/IF with text
=IF(AND(J6="X",K6="X",L6="X", M6="X"), IF(H:H<>"standard letter"), "Completed")
still trying!
RE: formulas with AND/IF with text
Hi Marie,
I will have a go and get back to you as soon as I have a solution.
Kind regards
Marius
RE: formulas with AND/IF with text
Hi Marie,
I hope I got this right:
=IF(AND(OR(A1="yes",A1="x"),OR(B1="yes",B1="x"),OR(C1="yes",C1="x"),D1<>"standard letter"),"completed","")
This will leave the result blank if the text "standard letter" appears in a certain column.
Kind regards
Marius
RE: formulas with AND/IF with text
Hi Marius,
Thanks so much for your time.
Unfortunately the formula produces a blank in all eventualities.
=IF(AND(OR(A1="yes",A1="x"),OR(B1="yes",B1="x"),OR(C1="yes",C1="x"),D1<>"standard letter"),"completed","")
In the spreadsheet - the text "standard letter" might arise in column H.
Therefore I have tries substituting D1 for H1 and also for H:H.
Still no joy.
Perhaps it's just a matter of getting spaces and other characters, right?
Thanks a million, again,
Marie
RE: formulas with AND/IF with text
Even tries this one but nothing: =IF(AND(OR(A1="yes",A1="x"),OR(B1="yes",B1="x"),OR(C1="yes",C1="x"), H:H<>"standard letter"),"completed","")
RE: formulas with AND/IF with text
Hi Marie,
Best practice is not to reference an entire column but rather a cell and then to copy it down. It is well worth checking for hidden spaces too.
Double check the cell references in your formula.
Good luck!
Marius
