Multiple Statements

P

petermccallion

New Member
Joined
Mar 22, 2012
Messages
11
Why will my formula not return a blank cell if the statements not met.

=IF(AND(I17="ü",K17="ü",M17="ü",S17="ü",U17="ü"),IF(OR(O17="ü",Q17="ü"),IF(OR(W17="ü",Y17="ü",AA17="ü"),"ü","")))
the "ü" is a windings font for a tick symbol which if the conditions are works ok but if the conditions are not met returns FALSE but i would prefer the cell to be blank, which I believe ,"ü","") at the end of the if statement should return. i have tried &"" after the formula but this has no effect.

any ideas would be greatly appreciated.

thanks peter
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
The logic in the formula is not the logic you want, but we can't fix the formula if you don't tell us the logic you want.

Let's lay out the formula a bit differently to see how the logic works:

Rich (BB code): 
=IF(AND(I17="ü",K17="ü",M17="ü",S17="ü",U17="ü"),
    IF(OR(O17="ü",Q17="ü"),
       IF(OR(W17="ü",Y17="ü",AA17="ü"),
          "ü", you get this result if all three of the preceding conditions are TRUE
          "") you this result if the first two conditions are TRUE but the third condition is FALSE
       ) you do not specify what happens if OR(O17="ü",Q17="ü") is FALSE, so if that happens it returns FALSE
    ) you do not specify what happens if AND(I17="ü",K17="ü",M17="ü",S17="ü",U17="ü") is FALSE, so when that happens it returns FALSE.

My best guess for what you want is this. This says if the AND is TRUE and both ORs are TRUE, the result is "ü", otherwise blank.

Excel Formula: 
=IF(AND(I17="ü",K17="ü",M17="ü",S17="ü",U17="ü",OR(O17="ü",Q17="ü"),OR(W17="ü",Y17="ü",AA17="ü")),"ü","")

If my suggestion is not what you want, then you need to spell out the logic you want to apply.
 
Upvote 0
Hi Rich thanks for your answer, I have tried your formula which did return a blank cell unfortunately the result if the conditions were met also returned a blank cell
The result "u" should be returned if all conditions are met and the cell blank if any one of the conditions are not met
 
Upvote 0
Who is Rich?

We now have two formulas that do not work, but no statement of your conditions. Please describe your conditions in words.
 
Upvote 0
If all cells I7, K7,M7,S7 and U7 = "u" (Condition 1)

And either O7 or Q7 = "u"
(Condition 2)

And either W7 or Y7 or AA7 = "u"
(Condition 3)

If all 3 conditions are true the result is "u"

If any of the 3 conditions are false the cell should be blank
 
Upvote 0
That formula does exactly that. Please provide sample data pasted into a post as a table, or a link to a file on a cloud data-sharing service.
 
Upvote 0
ClaimedUnit 301YUnit 302YUnit 303YUnit 304YUnit 305YUnit 308YUnit 309YUnit 904YUnit 905YUnit 906Y
üA3aMeritüPassüDistinctionüDistinctionüPassoMeritüPassüPassü
ü
Correct as I7="ü",K7="ü",M7="ü",S7="ü",U7="ü"
one of S7 orU7 has been checked
and one of W7, Y7,AA7 has been checked​
A3aPassüPassüMeritüMeritüN/AoPassüPassüPassüPass
üA3aPassüPassüPassüPassüN/AoMeritüDistinctionüPassü
üA3aDistinctionüDistinctionüDistinctionüDistinctionüN/AoDistinctionüDistinctionüPassü
üA3aPassüPassüPassüMeritüN/AoPassüMeritüPassü
üA3aDistinctionüDistinctionüDistinctionüMeritüN/AoMeritüDistinctionüPassü
üA3aDistinctionüPassüDistinctionüPassüN/AoDistinctionüMeritüPassü
üA3aMeritüMeritüMeritüN/AoDistinctionüMeritüMeritüMeritü
üA3aDistinctionüDistinctionüDistinctionüDistinctionüN/AoDistinctionüDistinctionüPassü
üA3aDistinctionüMeritüDistinctionüDistinctionüN/AoDistinctionüMeritüPassü
üA3aMeritüMeritüDistinctionüPassüN/AoPassüPassüMeritü
FALSE
should be empty as not all criteria has been met​
A3bPassüPassüPassüN/AoMeritüMarkingoMarkingo
FALSEA3bMeritüMeritüPassüN/AüDistinctionüMarkingMarking
FALSEA3bPassüPassüMeritüN/AüMeritüMarkingMarking
FALSEA3bPassüMeritüMeritüN/AüMeritüMarkingMarking
FALSEA3bDistinctionüMeritüDistinctionüN/AüDistinctionüMarkingMarking
üA3bMeritüPassüDistinctionüN/AoMeritüDistinctionüPassüMeritü
FALSEA3bPassüPassüMeritüN/AoPassüMarkingMarking
üA3bMeritüPassüPassüN/AoDistinctionüMeritüPassüMeritü
FALSEA3bPassüPassüMeritüN/AoMeritoMarkingMarking
 
Upvote 0
I pasted this data into a worksheet and am getting no results. I think the columns don't map correctly. In this data what column is column I and what column is AA?
 
Upvote 0
Show formulas turned on
BCDEFGHIJKLMNOPQRSTUVWXYZAA
ClaimedUnit 301YUnit 302YUnit 303YUnit 304YUnit 305YUnit 308YUnit 309YUnit 904YUnit 905YUnit 906Y
=IF(AND(I7="ü",K7="ü",M7="ü",S7="ü",U7="ü"),IF(OR(O7="ü",Q7="ü"),IF(OR(W7="ü",Y7="ü",AA7="ü"),"ü","")))A3aMeritüPassüDistinctionüDistinctionüPassoMeritüPassüPassü
=IF(AND(I8="ü",K8="ü",M8="ü",S8="ü",U8="ü"),IF(OR(O8="ü",Q8="ü"),IF(OR(W8="ü",Y8="ü",AA8="ü"),"ü","")))
Correct as I7="ü",K7="ü",M7="ü",S7="ü",U7="ü"
one of S7 orU7 has been checked
and one of W7, Y7,AA7 has been checked		A3aPassüPassüMeritüMeritüN/AoPassüPassüPassüPass
=IF(AND(I9="ü",K9="ü",M9="ü",S9="ü",U9="ü"),IF(OR(O9="ü",Q9="ü"),IF(OR(W9="ü",Y9="ü",AA9="ü"),"ü","")))A3aPassüPassüPassüPassüN/AoMeritüDistinctionüPassü
=IF(AND(I10="ü",K10="ü",M10="ü",S10="ü",U10="ü"),IF(OR(O10="ü",Q10="ü"),IF(OR(W10="ü",Y10="ü",AA10="ü"),"ü","")))A3aDistinctionüDistinctionüDistinctionüDistinctionüN/AoDistinctionüDistinctionüPassü
=IF(AND(I11="ü",K11="ü",M11="ü",S11="ü",U11="ü"),IF(OR(O11="ü",Q11="ü"),IF(OR(W11="ü",Y11="ü",AA11="ü"),"ü","")))A3aPassüPassüPassüMeritüN/AoPassüMeritüPassü
=IF(AND(I12="ü",K12="ü",M12="ü",S12="ü",U12="ü"),IF(OR(O12="ü",Q12="ü"),IF(OR(W12="ü",Y12="ü",AA12="ü"),"ü","")))A3aDistinctionüDistinctionüDistinctionüMeritüN/AoMeritüDistinctionüPassü
=IF(AND(I13="ü",K13="ü",M13="ü",S13="ü",U13="ü"),IF(OR(O13="ü",Q13="ü"),IF(OR(W13="ü",Y13="ü",AA13="ü"),"ü","")))A3aDistinctionüPassüDistinctionüPassüN/AoDistinctionüMeritüPassü
=IF(AND(I14="ü",K14="ü",M14="ü",S14="ü",U14="ü"),IF(OR(O14="ü",Q14="ü"),IF(OR(W14="ü",Y14="ü",AA14="ü"),"ü","")))A3aMeritüMeritüMeritüN/AoDistinctionüMeritüMeritüMeritü
=IF(AND(I15="ü",K15="ü",M15="ü",S15="ü",U15="ü"),IF(OR(O15="ü",Q15="ü"),IF(OR(W15="ü",Y15="ü",AA15="ü"),"ü","")))A3aDistinctionüDistinctionüDistinctionüDistinctionüN/AoDistinctionüDistinctionüPassü
=IF(AND(I16="ü",K16="ü",M16="ü",S16="ü",U16="ü"),IF(OR(O16="ü",Q16="ü"),IF(OR(W16="ü",Y16="ü",AA16="ü"),"ü","")))A3aDistinctionüMeritüDistinctionüDistinctionüN/AoDistinctionüMeritüPassü
=IF(AND(I17="ü",K17="ü",M17="ü",S17="ü",U17="ü"),IF(OR(O17="ü",Q17="ü"),IF(OR(W17="ü",Y17="ü",AA17="ü"),"ü","")))A3aMeritüMeritüDistinctionüPassüN/AoPassüPassüMeritü
=IF(AND(I18="ü",K18="ü",M18="ü",S18="ü",U18="ü"),IF(OR(O18="ü",Q18="ü"),IF(OR(W18="ü",Y18="ü",AA18="ü"),"ü","")))
should be empty as not all criteria has been metA3bPassüPassüPassüN/AoMeritüMarkingoMarkingo
=IF(AND(I19="ü",K19="ü",M19="ü",S19="ü",U19="ü"),IF(OR(O19="ü",Q19="ü"),IF(OR(W19="ü",Y19="ü",AA19="ü"),"ü","")))A3bMeritüMeritüPassüN/AüDistinctionüMarkingMarking
=IF(AND(I20="ü",K20="ü",M20="ü",S20="ü",U20="ü"),IF(OR(O20="ü",Q20="ü"),IF(OR(W20="ü",Y20="ü",AA20="ü"),"ü","")))A3bPassüPassüMeritüN/AüMeritüMarkingMarking
=IF(AND(I21="ü",K21="ü",M21="ü",S21="ü",U21="ü"),IF(OR(O21="ü",Q21="ü"),IF(OR(W21="ü",Y21="ü",AA21="ü"),"ü","")))A3bPassüMeritüMeritüN/AüMeritüMarkingMarking
=IF(AND(I22="ü",K22="ü",M22="ü",S22="ü",U22="ü"),IF(OR(O22="ü",Q22="ü"),IF(OR(W22="ü",Y22="ü",AA22="ü"),"ü","")))A3bDistinctionüMeritüDistinctionüN/AüDistinctionüMarkingMarking
=IF(AND(I23="ü",K23="ü",M23="ü",S23="ü",U23="ü"),IF(OR(O23="ü",Q23="ü"),IF(OR(W23="ü",Y23="ü",AA23="ü"),"ü","")))A3bMeritüPassüDistinctionüN/AoMeritüDistinctionüPassüMeritü
=IF(AND(I24="ü",K24="ü",M24="ü",S24="ü",U24="ü"),IF(OR(O24="ü",Q24="ü"),IF(OR(W24="ü",Y24="ü",AA24="ü"),"ü","")))A3bPassüPassüMeritüN/AoPassüMarkingMarking
=IF(AND(I25="ü",K25="ü",M25="ü",S25="ü",U25="ü"),IF(OR(O25="ü",Q25="ü"),IF(OR(W25="ü",Y25="ü",AA25="ü"),"ü","")))A3bMeritüPassüPassüN/AoDistinctionüMeritüPassüMeritü
=IF(AND(I26="ü",K26="ü",M26="ü",S26="ü",U26="ü"),IF(OR(O26="ü",Q26="ü"),IF(OR(W26="ü",Y26="ü",AA26="ü"),"ü","")))A3bPassüPassüMeritüN/AoMeritoMarkingMarking
=IF(AND(I27="ü",K27="ü",M27="ü",S27="ü",U27="ü"),IF(OR(O27="ü",Q27="ü"),IF(OR(W27="ü",Y27="ü",AA27="ü"),"ü","")))A3bPassüPassüDistinctionüN/AoPassüMarkingMarking
=IF(AND(I28="ü",K28="ü",M28="ü",S28="ü",U28="ü"),IF(OR(O28="ü",Q28="ü"),IF(OR(W28="ü",Y28="ü",AA28="ü"),"ü","")))A3bPassüMeritüMeritüN/AoDistinctionüDistinctionüMeritüPassü
=IF(AND(I29="ü",K29="ü",M29="ü",S29="ü",U29="ü"),IF(OR(O29="ü",Q29="ü"),IF(OR(W29="ü",Y29="ü",AA29="ü"),"ü","")))B3
=IF(AND(I30="ü",K30="ü",M30="ü",S30="ü",U30="ü"),IF(OR(O30="ü",Q30="ü"),IF(OR(W30="ü",Y30="ü",AA30="ü"),"ü","")))B3
=IF(AND(I31="ü",K31="ü",M31="ü",S31="ü",U31="ü"),IF(OR(O31="ü",Q31="ü"),IF(OR(W31="ü",Y31="ü",AA31="ü"),"ü","")))B3
=IF(AND(I32="ü",K32="ü",M32="ü",S32="ü",U32="ü"),IF(OR(O32="ü",Q32="ü"),IF(OR(W32="ü",Y32="ü",AA32="ü"),"ü","")))B3
=IF(AND(I33="ü",K33="ü",M33="ü",S33="ü",U33="ü"),IF(OR(O33="ü",Q33="ü"),IF(OR(W33="ü",Y33="ü",AA33="ü"),"ü","")))B3
=IF(AND(I34="ü",K34="ü",M34="ü",S34="ü",U34="ü"),IF(OR(O34="ü",Q34="ü"),IF(OR(W34="ü",Y34="ü",AA34="ü"),"ü","")))B3
=IF(AND(I35="ü",K35="ü",M35="ü",S35="ü",U35="ü"),IF(OR(O35="ü",Q35="ü"),IF(OR(W35="ü",Y35="ü",AA35="ü"),"ü","")))B3
=IF(AND(I36="ü",K36="ü",M36="ü",S36="ü",U36="ü"),IF(OR(O36="ü",Q36="ü"),IF(OR(W36="ü",Y36="ü",AA36="ü"),"ü","")))B3
 
Upvote 0
You have the formulas in the wrong rows. In your first post you gave an example of a formula for row 7. So I gave you a corrected formula for row 7. But you put it in row 3 (or maybe 4, I can't really tell).

Here is my formula with your data. If you still don't think the results are right, indicate which row.

$scratch.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAA
26StringJazzer's FormulaUnit 301YUnit 302YUnit 303YUnit 304YUnit 305YUnit 308YUnit 309YUnit 904YUnit 905YUnit 906Y
3
4üA3aMeritüPassüDistinctionüDistinctionüPassoMeritüPassüPassü
5üA3aPassüPassüMeritüMeritüN/AoPassüPassüPassüPass
6üA3aPassüPassüPassüPassüN/AoMeritüDistinctionüPassü
7üA3aDistinctionüDistinctionüDistinctionüDistinctionüN/AoDistinctionüDistinctionüPassü
8üA3aPassüPassüPassüMeritüN/AoPassüMeritüPassü
9üA3aDistinctionüDistinctionüDistinctionüMeritüN/AoMeritüDistinctionüPassü
10üA3aDistinctionüPassüDistinctionüPassüN/AoDistinctionüMeritüPassü
11üA3aMeritüMeritüMeritüN/AoDistinctionüMeritüMeritüMeritü
12üA3aDistinctionüDistinctionüDistinctionüDistinctionüN/AoDistinctionüDistinctionüPassü
13üA3aDistinctionüMeritüDistinctionüDistinctionüN/AoDistinctionüMeritüPassü
14üA3aMeritüMeritüDistinctionüPassüN/AoPassüPassüMeritü
15 A3bPassüPassüPassüN/AoMeritüMarkingoMarkingo
16 A3bMeritüMeritüPassüN/AüDistinctionüMarkingMarking
17 A3bPassüPassüMeritüN/AüMeritüMarkingMarking
18 A3bPassüMeritüMeritüN/AüMeritüMarkingMarking
19 A3bDistinctionüMeritüDistinctionüN/AüDistinctionüMarkingMarking
20üA3bMeritüPassüDistinctionüN/AoMeritüDistinctionüPassüMeritü
21 A3bPassüPassüMeritüN/AoPassüMarkingMarking
22üA3bMeritüPassüPassüN/AoDistinctionüMeritüPassüMeritü
23 A3bPassüPassüMeritüN/AoMeritoMarkingMarking
Sheet21
Cell Formulas
RangeFormula
B4:B23B4=IF(AND(I4="ü",K4="ü",M4="ü",S4="ü",U4="ü",OR(O4="ü",Q4="ü"),OR(W4="ü",Y4="ü",AA4="ü")),"ü","")
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
835
arthurbr
arthurbr
T
  • Solved
Have formula repeat for a value in a cell then use a different formula for a value number of steps
Replies
1
Views
558
Tmoff
T
K
  • Solved
formula for the average of cells across columns with multiple conditions
Replies
4
Views
439
kckliodna
K
P
  • Solved
Complex Conditional Formatting IF Statement with Time Calculation (Digital Sign In/Out Spreadsheet)
Replies
8
Views
448
awoohaw
awoohaw
ricafonyat
  • Solved
Loans Payments Consolidated Cash Flow
Replies
1
Views
241
DRSteele
DRSteele

Forum statistics

Threads
1,215,109
Messages
6,123,137
Members
449,098
Latest member
Doanvanhieu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top