SharePoint: AND Function

Welcome to Function Friday. This week we’ll explore Microsoft’s SharePoint List Function “AND” function. This function will return “Yes” if all arguments are “true”. If any of them are not, it will return “No”.

Limitations

Max 30 conditional values. If you need more than 30, you should break it into smaller chunks.

Arrays

AND operations can be performed in bulk if you have an array with elements. For example, =AND({TRUE, FALSE}) will return No
You can’t perform operations within the array-like =AND({TRUE,1=1})or nest them =AND({TRUE,AND({TRUE,FALSE})})

References

SharePoint evaluates references to tables like =AND([isActive])
You can combine references like =AND([isActive], True). []identify the reference to the table, but they are not necessary if the name doesn’t contain spaces. So you can write the same formula like =AND(isActive, True)

Text

AND is case insensitive, so AND("TrUe") or AND("True") is evaluated to Yes
AND is language-specific, so AND("Falso")(False in Portuguese) will return the #Value!error.

Operations

Operations like =AND(1=2) are possible. Compatible with <, >, <=, >= or <>.
Arithmetic operations are also possible. For example, OR(1+1=2)will return Yes
You can nest operations like =AND(1<2,AND(1<2,TRUE))
You can combine with other operators like OR=AND(1<2,OR(1<2,TRUE))

Recommendations:

Avoid complex formulas. If you get a “#Value!” error, it will be hard to debug what is the condition that is generating the error.
If possible, test the conditions one by one to see if you’re getting the desired results. Only after you’re sure that all of them are correct, join them into a big list of conditions. By doing this, you are sure that each of them is returning the desired value.
I recommend no more than ten conditional values. Although SharePoint supports up to 30, it will become an unmanageable mess to debug complex formulas.