CODEWITHSUNDEEP
excelexcel-formula
user7531258
user7531258

Reputation: 37

How to use an IF statement for multiple cells?

I have this statement as the IF statement 

=IF(AND(C5,D5,E5,F5,G5,H5,I5= "x"),"1","0")

However it only marks a 1 if the last one has an x in it, I want it to check if whether those cells all have x then it should have a 1, otherwise it would be a 0.

Upvotes: 0

Views: 14657

Answers (3)

Alexis Olson
Alexis Olson

Reputation: 40204

You can do this with an array formula with a range as an argument:

=IF(AND(C5:I5="x"),"1","0")

(You'll need to press Ctrl+Shift+Enter to confirm it as an array formula.)

Upvotes: 3

pnuts
pnuts

Reputation: 59475

Without array formula:

=--(COUNTIF(C5:I5,"x")=6)&IF(,,)

Upvotes: 0

Chronocidal
Chronocidal

Reputation: 7951

The AND function treats all statements individually: AND(A1, A2="x") would mean "The value in A1 is TRUE and the value in A2 is 'x'" - to check if they are both "x", you need to explicitly state this in each statement: AND(A1="x", A2="x")

Bonus points - changing TRUE or FALSE to 1 or 0 is easy - just add a double negation (--) before the boolean: (And wrap in TEXT to change it to a string instead of a number)

=TEXT(--AND(C5="x", D5="x", E5="x", F5="x", G5="x", H5="x", I5="x"), "0")

Upvotes: 0

Related Questions