98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formulas with AND/IF with text
Formulas with AND/IF with text
Resolved · High Priority · Version 2016
Thu 14 Mar 2019, 11:19Delegate Marie said...
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
Thu 14 Mar 2019, 11:42Trainer Marius said...
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
Thu 14 Mar 2019, 12:00Delegate Marie said...
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?
Thu 14 Mar 2019, 12:05Delegate Marie said...
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")
Thu 14 Mar 2019, 12:08Delegate Marie said...
RE: formulas with AND/IF with text
or would the add on bit read:
=IF(B:B<>"standard letter","completed")
Thu 14 Mar 2019, 12:12Delegate Marie said...
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!
Thu 14 Mar 2019, 12:50Trainer Marius said...
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
Thu 14 Mar 2019, 13:24Trainer Marius said...
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
Thu 14 Mar 2019, 15:34Delegate Marie said...
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
Thu 14 Mar 2019, 15:43Delegate Marie said...
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","")
Thu 14 Mar 2019, 15:45Trainer Marius said...
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
Thu 21 Mar 2019: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Highlighting only Text cellsTo select onlt text value cells in a spreadsheet, click on Edit-Go to(F5) |
