IF statement for multiple IDs

Paul Evenson

I have a column with multiple IDs and I want to select specific IDs and "complete" = 1 else 0.

My current formula is :

=IF(CONTAINS("000", [GAP Status ID]@row, 1, IF(CONTAINS("010", [GAP Status ID]@row, 1, IF(CONTAINS("020", [GAP Status ID]@row, 1, IF(CONTAINS("100", [GAP Status ID]@row, 1, IF(CONTAINS("110", [GAP Status ID]@row, 1, IF(CONTAINS("120", [GAP Status ID]@row, 1, IF(CONTAINS("130", [GAP Status ID]@row, 1, IF(CONTAINS("140", [GAP Status ID]@row, 1, IF(CONTAINS("150", [GAP Status ID]@row, 1, IF(CONTAINS("160", [GAP Status ID]@row, 1, IF(CONTAINS("900", [GAP Status ID]@row, 1, 0))))))))))))))))))))))

I get the error #INCORRECT ARGUEMNET SET. Not sure what I am doing wrong.

heyjay

Close your Contains with )

=
IF(CONTAINS("000", [GAP Status ID]@row), 1, 
IF(CONTAINS("010", [GAP Status ID]@row), 1, 
IF(CONTAINS("020", [GAP Status ID]@row), 1, 
IF(CONTAINS("100", [GAP Status ID]@row), 1, 
IF(CONTAINS("110", [GAP Status ID]@row), 1, 
IF(CONTAINS("120", [GAP Status ID]@row), 1, 
IF(CONTAINS("130", [GAP Status ID]@row), 1, 
IF(CONTAINS("140", [GAP Status ID]@row), 1, 
IF(CONTAINS("150", [GAP Status ID]@row), 1, 
IF(CONTAINS("160", [GAP Status ID]@row), 1, 
IF(CONTAINS("900", [GAP Status ID]@row), 1, 
0)

We can further simplify:

=IF(OR(
CONTAINS("000", [GAP Status ID]@row), 
CONTAINS("010", [GAP Status ID]@row), 
CONTAINS("020", [GAP Status ID]@row), 
CONTAINS("100", [GAP Status ID]@row), 
CONTAINS("110", [GAP Status ID]@row), 
CONTAINS("120", [GAP Status ID]@row), 
CONTAINS("130", [GAP Status ID]@row), 
CONTAINS("140", [GAP Status ID]@row), 
CONTAINS("150", [GAP Status ID]@row), 
CONTAINS("160", [GAP Status ID]@row), 
CONTAINS("900", [GAP Status ID]@row)
), 1, 0)

heyjay

Close your Contains with )

=
IF(CONTAINS("000", [GAP Status ID]@row), 1, 
IF(CONTAINS("010", [GAP Status ID]@row), 1, 
IF(CONTAINS("020", [GAP Status ID]@row), 1, 
IF(CONTAINS("100", [GAP Status ID]@row), 1, 
IF(CONTAINS("110", [GAP Status ID]@row), 1, 
IF(CONTAINS("120", [GAP Status ID]@row), 1, 
IF(CONTAINS("130", [GAP Status ID]@row), 1, 
IF(CONTAINS("140", [GAP Status ID]@row), 1, 
IF(CONTAINS("150", [GAP Status ID]@row), 1, 
IF(CONTAINS("160", [GAP Status ID]@row), 1, 
IF(CONTAINS("900", [GAP Status ID]@row), 1, 
0)

We can further simplify:

=IF(OR(
CONTAINS("000", [GAP Status ID]@row), 
CONTAINS("010", [GAP Status ID]@row), 
CONTAINS("020", [GAP Status ID]@row), 
CONTAINS("100", [GAP Status ID]@row), 
CONTAINS("110", [GAP Status ID]@row), 
CONTAINS("120", [GAP Status ID]@row), 
CONTAINS("130", [GAP Status ID]@row), 
CONTAINS("140", [GAP Status ID]@row), 
CONTAINS("150", [GAP Status ID]@row), 
CONTAINS("160", [GAP Status ID]@row), 
CONTAINS("900", [GAP Status ID]@row)
), 1, 0)

Paul Evenson

Thank you @heyjay ! That was it!!